Forum Discussion
Helper IIIRow level similarity rate on multiple columns
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
Super Userbigk Here the code for similarity in pairs:
let
Source = #"Key info - Basic info",
#"Removed Other Columns" = Table.SelectColumns(Source,{"EntityId"}),
// Append Portfolio Excel
#"Load_Portfolio_Excel has All In ID" = #"Project portfolio 20-24 excel",
SelectedColumns1 = Table.SelectColumns(#"Load_Portfolio_Excel has All In ID", {"All in ID"}),
#"Changed Type1" = Table.TransformColumnTypes(SelectedColumns1,{{"All in ID", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"All in ID", "EntityId"}}),
#"Appended Query1" = Table.Combine({#"Removed Other Columns", #"Renamed Columns1"}),
#"Removed Duplicates1" = Table.Distinct(#"Appended Query1"),
// Merged with OneFico costs table
#"Load OneFico Costs" = #"ICT costs OneFico",
SelectedColumns2 = Table.SelectColumns(#"Load OneFico Costs", {"All in ID"}),
#"Changed Type2" = Table.TransformColumnTypes(SelectedColumns2,{{"All in ID", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"All in ID", "EntityId"}}),
#"Removed Duplicates5" = Table.Distinct(#"Renamed Columns2"),
#"Appended Query2" = Table.Combine({#"Removed Duplicates1", #"Removed Duplicates5"}),
#"Removed Duplicates2" = Table.Distinct(#"Appended Query2"),
// Merged with Master file Main sheet
#"Load main master sheet" = #"Main master sheet",
SelectedColumns3 = Table.SelectColumns(#"Load main master sheet", {"All In"}),
#"Changed Type3" = Table.TransformColumnTypes(SelectedColumns3,{{"All In", type text}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"All In", "EntityId"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns3"),
#"Appended Query3" = Table.Combine({#"Removed Duplicates2", #"Removed Duplicates"}),
#"Removed Duplicates3" = Table.Distinct(#"Appended Query3"),
// Merge all tables returning names
#"Merged Portfolio xl" = Table.NestedJoin(#"Removed Duplicates3", {"EntityId"}, #"Project portfolio 20-24 excel", {"All in ID"}, "Project portfolio 20-24 excel", JoinKind.LeftOuter),
#"Expanded Project portfolio 20-24 excel" = Table.ExpandTableColumn(#"Merged Portfolio xl", "Project portfolio 20-24 excel", {"Project name"}, {"PortfolioXL.Name"}),
#"Remove duplicates" = Table.Distinct(#"Expanded Project portfolio 20-24 excel"),
// Merge with Main master sheet
MergedMasterSheet = Table.NestedJoin(#"Remove duplicates", {"EntityId"}, #"Main master sheet", {"All In"}, "MasterdataSheet", JoinKind.LeftOuter),
#"Expanded MasterdataSheet" = Table.ExpandTableColumn(MergedMasterSheet, "MasterdataSheet", {"Name in SAP"}, {"MasterdataSheet.Name"}),
// Merge with All In names
MergedAllin = Table.NestedJoin(#"Expanded MasterdataSheet", {"EntityId"}, #"Key info - Basic info", {"EntityId"}, "Key info - Basic info", JoinKind.LeftOuter),
#"Expanded Key info - Basic info" = Table.ExpandTableColumn(MergedAllin, "Key info - Basic info", {"Project name"}, {"All In.Name"}),
// Uppercase all name columns
#"Uppercased Text" = Table.TransformColumns(#"Expanded Key info - Basic info",{{"PortfolioXL.Name", Text.Upper, type text}, {"MasterdataSheet.Name", Text.Upper, type text}, {"All In.Name", Text.Upper, type text}}),
#"Removed Duplicates4" = Table.Distinct(#"Uppercased Text"),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Duplicates4",{{"PortfolioXL.Name", type text}, {"MasterdataSheet.Name", type text}, {"All In.Name", 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 Type4", "Similarity 1-2", each BigramSimilarity([All In.Name], [MasterdataSheet.Name]), type number),
#"Added Similarity 1-3" = Table.AddColumn(#"Added Similarity 1-2", "Similarity 1-3", each BigramSimilarity([All In.Name], [PortfolioXL.Name]), type number),
#"Added Similarity 2-3" = Table.AddColumn(#"Added Similarity 1-3", "Similarity 2-3", each BigramSimilarity([MasterdataSheet.Name], [PortfolioXL.Name]), type number)
in
#"Added Similarity 2-3"
the total similarity was calculated using a union-based Jaccard similarity over all three columns combined.
BBF
Helper IIIThe similarity generates number above 1 which should not be the case. for example. I would expect 100% match between 2 and 3 instead of 150%