# Fuzzy Matching/Lookup with Power Query



## Dane0987 (Mar 28, 2017)

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 *)

%Australia%Australia%China%China%Brazil%Brazil

<tbody>

</tbody>
Fact table: 


Text stringCountry35 Stirling Highway Perth WA 6009,AustraliaAustraliaParque Cidade (Brazil) Corporate SCSBrazil

<tbody>

</tbody>







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 (Mar 28, 2017)

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 (Mar 28, 2017)

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.


----------

