Row level similarity rate on multiple columns

bigk 

Jaccard Similarity only considers character sets, so it ignores letter order. That’s why you’re seeing a similarity of 1 when words contain the same letters but in different positions.

Since letter position matters, try to use the bigram similarity:

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMlTSUfLKz8hTcEJmOCmAmEqxOtFKRkCub2JRpYKbHpDlpqcA5GSmQgRBoqV6YGVo5kBpf5AhsQA=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name1 = _t, Name2 = _t, Name3 = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"ID", Int64.Type}, {"Name1", type text}, {"Name2", type text}, {"Name3", type text}}),

// Function to generate bigrams (2-letter sequences)
GenerateBigrams = (text as nullable text) as list =>
let
cleanText = Text.Lower(Text.Trim(if text = null then "" else text)),
chars = Text.ToList(cleanText),
bigrams = List.Transform({0..List.Count(chars)-2}, each Text.Combine(List.FirstN(List.Skip(chars, _), 2)))
in
if List.Count(chars) < 2 then {cleanText} else bigrams,

// Function to compute bigram similarity
BigramSimilarity = (text1 as nullable text, text2 as nullable text) as number =>
let
bigrams1 = GenerateBigrams(text1),
bigrams2 = GenerateBigrams(text2),
intersection = List.Intersect({bigrams1, bigrams2}),
union = List.Distinct(List.Combine({bigrams1, bigrams2})),
similarity = if List.Count(union) = 0 then 0 else Number.Round(List.Count(intersection) / List.Count(union), 2)
in
similarity,

// Compute similarity for each pair of columns
#"Added Similarity 1-2" = Table.AddColumn(#"Changed Type", "Similarity 1-2", each BigramSimilarity([Name1], [Name2]), type number),
#"Added Similarity 1-3" = Table.AddColumn(#"Added Similarity 1-2", "Similarity 1-3", each BigramSimilarity([Name1], [Name3]), type number),
#"Added Similarity 2-3" = Table.AddColumn(#"Added Similarity 1-3", "Similarity 2-3", each BigramSimilarity([Name2], [Name3]), type number)
in
#"Added Similarity 2-3"

BBF