Forum Discussion
Fuzzy Lookup in Power BI
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
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 LearnIf 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.
10 Replies
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 LearnIf 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.
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?
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
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).
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?
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
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.
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.
