Fuzzy Lookup in Power BI

[cmbcmb]

Hello,

Novice Excel and Power BI user here.

I want to compare two datasets, one containing data on all UK businesses (address, company type etc - almost 3 million rows on Power BI) and the other being our company's current tenants (200 rows on Excel). I want to extract the information from the larger dataset to populate the table showing our 200 tenants. I initially tried importing my table from Power BI into excel to perform a fuzzy lookup but quickly realised the row limit was 30k and so my dataset was incomplete.

Could anyone advise on how I could perform the same in Power BI? 

- Layman's terms please!

Thanks

[ZhangKun]

It seems that you have solved the problem of fuzzy matching?

Although Power BI export data is limited to 30,000 rows, you can export all of them through external tools (use DAX Studio to export data in multiple times).

[cmbcmb]

Hi yes, I suppose I could do this - although it would be a lot of manual work. Also if comparing more than one dataset (in this case 100) would this result in mutliple output colums or could this be condensed into one?

[v-kpoloju-msft · Accepted solution]

Hi @cmbcmb,

Thank you for reaching out to the Microsoft Fabric Community Forum.

We really apologies for the inconvenience, after reviewing the issue of fuzzy lookup in power bi, here are few steps to may resolve the issue.

• Please import both datasets into power bi by selecting get data and choosing the appropriate data sources. Excel for your tenants and the larger dataset for uk businesses. After the data is loaded, select transform data to access the power query editor.
• In the power query editor, navigate to the home tab and select merge queries. Choose the two tables you wish to merge and select the columns for comparison, such as address or company type. At the bottom of the merge dialog box, ensure to check the option for using fuzzy matching to perform the merge.
• Please click on the fuzzy matching options link to adjust the similarity threshold and other settings. The similarity threshold defines how closely the values need to match. A lower threshold will result in more matches, while a higher threshold will be more restrictive.
• Once you have configured the fuzzy matching options, click ok to proceed with the merge. Review the merged results to ensure they align with your expectations. If necessary, you can adjust the similarity threshold and other settings. Finally, click close & apply to save the changes and load the data back into power bi.

Also, I provided some documents related to the thread. Please go through the below following links for better understanding:
How fuzzy matching works in Power Query - Power Query | Microsoft Learn
Create a fuzzy match (Power Query) – Microsoft  Support
Fuzzy merge - Power Query | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[cmbcmb]

Fantastic thank you! If I want to pull the data from the larger table into the smaller table how would I go about this? Do I need to select a specific join kind?

[cmbcmb]

I've solved this - my next question would be: would it be possible to compare ONE column in one table against TWO columns in another. E.g. Lease Name in Table One with Lease Name AND Trading Name in Table Two. Or would I have to combine these two columns in some way?

[PwerQueryKees]

You cant fuzzy match on two columns directly.

BUT

You can Unpivot your Lease Name and Trading Name columns in the lookup table.
Then fuzzy match on the Value column.

Mind the fact that this might match your original table with 2 row in the unpivoted table, so you may need some extra step to deal with that.

[PwerQueryKees]

In most cases the left-outer is used. It will keep all the rows in your first join table and collect the matching rows from your second table.

right-outer work the same only from second table to first table. I have never used it 🙂

If you want only the rows from the first table that match at least one row from the second, use inner. I use it normally to filter the first table 

And full outer gives you all the rows in the first AND all rows from the second.

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[Greg_Deckler]

@cmbcmb I'm not understanding the 30K row limit? Are you saying that when doing a Merge operation and using Fuzzy matching that there is a limit of 30K rows? I created a DAX fuzzy matching solution once: Fuzzy - Microsoft Fabric Community

[cmbcmb]

Hi Greg,

When importing the csv I extracted from Power BI into Excel I got a pop up saying that the file was too large and this may result in some data sampling.

I ignored this and later realised my excel sheet contained only 30,000 rows as opposed to the almost 3 million in Power BI, meaning the dataset was incomplete.

[kmsmith1108]

I recently experienced the same thing.  Working with a query in Power BI and then not being able to export the work due to a 30K Export Limit.  Same "Data Sampling" message.   Essentially wasted a considerable amount of time.