Compare two strings for similarity

[amjwh99]

Hi,

Is there a way to compare two text strings for similarity? I'm looking for the return value to be a percentage, not true/false.  Example

[v-sihou-msft · Accepted solution]

@amjwh99

In DAX/Power Query, we can't check consequence of each character in comparison, like "John" and "Jonh". If you don't need to consider that kind of scenario, you just need to have each character in Name2 compare with Name1 and get the number of matched chrarcters for calculation percentage. 

1. Transfrom Name2 into a List. 

=Text.ToList([Name2])

2. Expand values into current table. 

3. Add a custom column to check if character is matched.

=if Text.Contains([Name1],[Name2 List]) then 1 else 0

4. Group on Name1 and Name2. 

5. Create a column to Name1 text length.

=Text.Length([Name1])

6. Create a column to calculate the similarity.

Then entire Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSgVCxOtFKvpnZqUA+mALxvRLBfK/E5PwksIBPYlFxFVAERCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name1 = _t, Name2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name1", type text}, {"Name2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Name2 List", each Text.ToList([Name2])),
    #"Expanded Name2 List" = Table.ExpandListColumn(#"Added Custom1", "Name2 List"),
    #"Added Custom" = Table.AddColumn(#"Expanded Name2 List", "Custom", each if Text.Contains([Name1],[Name2 List]) then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Name1", "Name2"}, {{"Match", each List.Sum([Custom]), type number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "NameLength", each Text.Length([Name1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Similarity", each [Match]/[NameLength]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Similarity", Percentage.Type}})
in
    #"Changed Type1"

Regards,