I have a list of company names - I've done a lot of cleaning up to get to this list. Some of the company names are slightly different like 2J Antennas, s.r.o. and 2J Antennas USA, Corporation or all the companies below are the same.
Is there a way to use fuzzymatch on the same list? I've tried copying the data to create 2 lists but it just returns the 1.0 matches for everything.
Am i missing something there is there not really a way to do this?
Thanks so much!
51 Answer
You can select a cell in your list, then use Data>Get & Transform Data>From Table/Range.
This will open the Power Query Editor:
Change the type of the column to Text by clicking the data type icon in the top left of the column header.
Then, go to Home>Advanced Editor> and modify the query by adding a FuzzyGroup transformation:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), #"FuzzyGroup" = Table.FuzzyGroup(#"Changed Type","Data",{{"Count",each Table.RowCount(_), Int64.Type}},[Threshold=0.2])
in #"FuzzyGroup"Note that you must:
- Add a comma to the end of the "Changed Type" line
- Add the "FuzzyGroup" line (call it whatever you want)
- Change the "in" part to say "FuzzyGroup" (this must match whatever name you chose in step 2
- Add optional parameters inside the square brackets to configure your fuzzy group. The default Threshold is, I believe, 0.8. That doesn't really help with your example data, so I've changed it to 0.2. Experiment with different values of Threshold and be careful that you don't create false positives.
More details here.
