Power Query Fuzzy Lookup across one row

[sauzee]

Hi All

I'm trying to get all fuzzy lookups within a row to help with analysis. This thread has proved very helpful

Identify fuzzy matches in one column

Hello! I have a list of 20k names and I am trying to find the fuzzy duplicates on the same column Like: Company Waterfall Company Waterfll Company Waterfalls Ideally those 3 would be highlighted, marked with a 1 or something that allows for identification Any ideas? been looking for weeks...

www.mrexcel.com

But I'm struggling to exactly implement the solution. I have attached a much smaller version of the table and my current code up to the merge and fuzzy match is below. Many thanks!

let
    Source = Excel.CurrentWorkbook(),
    Table1 = Source{[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Table1,{{"Name1", type text}, {"Count1", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Name1"}, #"Changed Type", {"Name1"}, "Changed Type", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.85, SimilarityColumnName="Similarity"]),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Changed Type", "Table"}})
in
    #"Renamed Columns"

 

[Alex Blakenburg]

OK lets see how we go with this.

1) Load your Table1 as a connection only query and in the right hand pane give it the name "Table1_2nd_Load" (without the quotes)
(that name is used in the code below)

2.1) Now either load the same table to Power Query again OR create a blank query.
2.2) Open the Advanced Editor of this query and replace everything you see there with the code below.
2.3) Close and Load To (select where to load it to, ideally give the query a meaningful name first)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name1", type text}, {"Count1", Int64.Type}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Name1"}, Table1_2nd_Load, {"Name1"}, "Table1_2nd_Load", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.80]),
    AddCustomColAsList = Table.AddColumn(#"Merged Queries", "Name Match", each List.Sort([Table1_2nd_Load][Name1])),
    #"Removed Columns" = Table.RemoveColumns(AddCustomColAsList,{"Table1_2nd_Load"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Name Match", each Text.Combine(List.Transform(_, Text.From), "|"), type text})
in
    #"Extracted Values"

 

[sauzee]

OK lets see how we go with this.

1) Load your Table1 as a connection only query and in the right hand pane give it the name "Table1_2nd_Load" (without the quotes)
(that name is used in the code below)

2.1) Now either load the same table to Power Query again OR create a blank query.
2.2) Open the Advanced Editor of this query and replace everything you see there with the code below.
2.3) Close and Load To (select where to load it to, ideally give the query a meaningful name first)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name1", type text}, {"Count1", Int64.Type}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Name1"}, Table1_2nd_Load, {"Name1"}, "Table1_2nd_Load", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.80]),
    AddCustomColAsList = Table.AddColumn(#"Merged Queries", "Name Match", each List.Sort([Table1_2nd_Load][Name1])),
    #"Removed Columns" = Table.RemoveColumns(AddCustomColAsList,{"Table1_2nd_Load"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Name Match", each Text.Combine(List.Transform(_, Text.From), "|"), type text})
in
    #"Extracted Values"

Yes that's brilliant! Thanks so much

 

[Alex Blakenburg]

You're welcome. Glad I could help.