Table.FuzzyNestedJoin M Function Fuzzy Matching in Power BI Power Query
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about “approximate string comparison logic“ popularly known as “fuzzy matching”
Fuzzy matching allows users to compare items in separate tables and join them if they are closely related
In the caption below, we have two tables. We supposedly have 17 Regions in column A and 11 Regions in column B. When we look closely to the two table, we discovered among other things, thus:
- All the Regions in column A have proper case text while in column C, we have upper case text.
- We have Australia & Oceania in column A and “AUSTRALIA AND OCEANIA” in column C respectively
- In column A, we have “Sub Saharan Africa” while we have “SUB-SAHARAN AFRICA” in column C
Essentially, we want to both Regions from both tables that are closely closely related
Note, the two data are stored in Excel Table and descriptive Table names are follow – Data1 and Data2, are provided. In addition, we have already loaded the data into the Power BI Power Query as seen in the caption below
In the Merge Queries dropdown of the Combine group of the Home tab, click on Merge Queries As New
In the Merge dialogue box, click select Data1 from the dropdown and select Region column in the preview
In the Merge dialogue box, click select Data2 from the dropdown and select Region column in the preview
The Join Kind is Left Outer (all from first, matching from second)
Check Use fuzzy matching to perform the merge
Click on the Fuzzy matching options
In the Similarity Threshold box, type in 0.5. Note, 0.00 matches all values in both table while 1.00 performs exact matching
In the Maximum number of matches, we specified 1
Click OK. We have the Table below
Click on the double upward arrows to expand
Uncheck Use original as column name as prefix
Click OK
From the caption above, we discovered that
- In Data2.Region column, ASIA was merged with Asia Tiger because we have Asia in Region column
- Also, we have SUB-SAHARAN AFRICA matched with Saharan etc