Fuzzy matching on textual data in Power Query

[absherzad]

Hello everyone,
Is there a way to incorporate Microsoft Fuzzy Lookup Add-In for Excel in Power Query to perform fuzzy matching on textual data in Power Query? Or is it possible to calculate string similarity in Power Query?

 

[Drewbbc]

let
Table1 = #table(type table [value = text],
{ {"spruce"}, {"cedar"}, {"birch"}, {"pine"}, {"poplar"}, {"aspen"},
{"baobab"}, {"larch"}, {"willow"} }),

Table2 = #table(type table [word = text],
{ {"sprce"}, {"cidar"}, {"pane"}, {"aspern"}, {"babab"} }),

AddCol = Table.AddColumn(Table2, "Custom", each Table1),
Expand = Table.ExpandTableColumn(AddCol, "Custom", {"value"}, {"value"}),

FuzzyCalc = Table.AddColumn(Expand, "Prct", each
2 * List.Count( List.Intersect(
{ Text.ToList([word]), Text.ToList([value]) } ) )
/ (List.Count(Text.ToList([word])) + List.Count(Text.ToList([value])))),

SortRows = Table.Sort(FuzzyCalc,{{"word", Order.Ascending}, {"Prct", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Index", 1, 1),
RemoveDupls = Table.Distinct(AddIndex, {"word"})
in
RemoveDupls

 

[Drewbbc]

Andvg - another approach using Cartesian product.

let
Src_A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Src_B = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
AddCol_Cartesian = Table.AddColumn(Src_B, "Custom", each Src_A),
Expand = Table.ExpandTableColumn(AddCol_Cartesian, "Custom", {"Words1"}, {"Words1"}),

FuzzyCalc = Table.AddColumn(Expand, "Prct", each 2 * List.Count(
List.Intersect( { Text.ToList([Words2]), Text.ToList([Words1]) }
) ) / (List.Count(Text.ToList([Words2])) + List.Count(Text.ToList([Words1])))),

SortRows = Table.Sort(FuzzyCalc,{{"Words2", Order.Ascending}, {"Prct", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Index", 1, 1),
RemoveDups = Table.Distinct(AddIndex, {"Words2"}),
RemoveCol = Table.RemoveColumns(RemoveDups,{"Index"})
in
RemoveCol