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]

I am a bit confused, are you joining the table to itself ? Do you only have 1 table ?

 

[sauzee]

I am a bit confused, are you joining the table to itself ? Do you only have 1 table ?

Yes, it could be set up as two identical tables. But it's one list of names and I'm trying to find the fuzzy matches within that one list. So made sense just to fuzzy lookup the initial table to itself.

I could break it into two tables by perhaps including one table with a high count and then match it against the names with a lower count. But I'm intrigued to see the full list first.

Thanks

 

[Alex Blakenburg]

Not sure how well that will work for you. If you set a high threshold it will only return itself or all exact matches.
If you set a lower threshold you are going to get multiple records for each line.

What are you hoping to achieve ?

 

[sauzee]

Not sure how well that will work for you. If you set a high threshold it will only return itself or all exact matches.
If you set a lower threshold you are going to get multiple records for each line.

What are you hoping to achieve ?

Yes it will create a problem with one name being matched to another, then that same name being matched back the other way. That's something I'll just have to go through manually.

It will be also match each name with itself but these will be easy enough to filter out

Thanks

 

[Alex Blakenburg]

So what is it doing or not doing, that you want us to look at ?

 

[sauzee]

So what is it doing or not doing, that you want us to look at ?

So what is it doing or not doing, that you want us to look at ?

You outlined a solution to get all matches across one row here

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

Getting all the matches on one row would be great (which I can then split by delimeter)

but I couldn't quite get the code you mentioned here to create a table like that

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

I'm not sure where where I'm going wrong, it seems pretty straightforward but I just couldn't replicate the output you got

Thanks

 

[sauzee]

So something like this (from the names in my earlier example)

Getting rid of the duplicates would be incredible but I honest can't visualise how to do that yet. Creating a helper column of all the separate strings then filtering out duplicates should be possible. But investigate that after!

Thanks

 

[Alex Blakenburg]

Its pretty late here, so I will have a look tomorrow.
If you can give me a bit more data via an XL2BB that would be great, otherwise I will try retyping just the 3 lines in your screenshot.

 

[sauzee]

Yes of course. Here's a table with a few more example.

Name1	Count1
Jose A Hernandez	81
Jose A. Fernandez	1
Micheal Carter-Demary	35
Micheal Carter Demary	2
Christian Iglesias	5
Gaetan Weydts Caessens	5
Phillip Mac OGrady	5
Jose A. Hernandez	5
Michael Carter Demary	351
Iglesias Cristian	25
Cristian Iglesias	22
Gaetan Weydts-Caesens	50
Gaetan Weydts Caesens	2
Phillip Mac O'Grady	122
Phillip Mac O''Grady	2

Many thanks, I really appreciate the help!