Check similarities between two columns in percentage

[Daven]

Hi,

I have two columns and would like to create a new column that would show how similar are the two columns. The order of the words does not matter. If all words are contained in Name2 from Name1, it would be 100% match. i.e below

Name1	Name2	Similarity_% (New column)
Pepsi Co	Pepsi Co	100%
Co Pepsi	Pepsi Co	100%
Pepsi Co LTD	Pepsi Co	90%
Pepsi Co	Cola	0%

Thanks,

Daven

[AlexisOlson · Accepted solution]

If you're doing this in the query editor, you can write a custom column with this formula

let
    a = List.Distinct(Text.Split([Name1], " ")),
    b = List.Distinct(Text.Split([Name2], " "))
in
    List.Count(List.Intersect({a, b})) / List.Count(a)

This gives the fraction of the number of words that the names share divided by the number of words in Name1 (so 67% for your 3rd row--I don't know where your 90% came from).

This is likely possible in DAX but more difficult without an analog for Text.Split.