Fuzzy Matching/Lookup with Power Query

[Dane0987]

Hi Guys,

Having trouble writing a power query function to accomplish fuzzy matching/lookup. Would really appreciate any help.

Here's the scenario:

Lookup table:
This table contains the SQL like operator (I think this can be used in M-code. In Excel I would use *)
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]%Australia%[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]%China%[/TD]
[TD]China[/TD]
[/TR]
[TR]
[TD]%Brazil%[/TD]
[TD]Brazil[/TD]
[/TR]
</tbody>[/TABLE]

Fact table:

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Text string[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]35 Stirling Highway Perth WA 6009,Australia[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]Parque Cidade (Brazil) Corporate SCS[/TD]
[TD]Brazil[/TD]
[/TR]
</tbody>[/TABLE]








I essentially want to search for a keyword (found in lookup table) in any text string in the fact table and apply the result in a new column in my fact table (Country).

I can do this in Excel with this type of formula: =LOOKUP(2^15,SEARCH(D$2:D$6,A2),E$2:E$6).

But when the lookup and fact tables are large then the formula is slow to calculate. That''s why I wanna do this in Power Query. However, I'm a complete beginner with Power Query. Still learning formulas

 

[Craigc3814]

Maybe not the answer you are looking for but this add-in should be faster than your formula and its free to use

https://www.microsoft.com/en-us/download/details.aspx?id=15011

 

[Dane0987]

Thanks Craig. Appreciate the prompt reply. The Fuzzy Lookup add-in is a great tool which I often use. But I think its based on the Jaccard Index formula which would mean the text string length is important. In my case, I may have a really long text string such as an address - so one keyword somewhere in there could give me the associated country, for example.