Fuzzy Matching In Power BI Power Query

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:

  1. All the Regions in column A have proper case text while in column C, we have upper case text.
  2. We have Australia & Oceania in column A and “AUSTRALIA AND OCEANIA” in column C respectively
  3. 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

  1. In Data2.Region column, ASIA was merged with Asia Tiger because we have Asia in Region column
  2. Also, we have SUB-SAHARAN AFRICA matched with Saharan etc

 

 

Posted in Blog and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *