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