Excel for Office 365: Fuzzy Matches with the Power Query Editor by Chris Menard

Posted on:  12/30/2019
Excel for Office 365: Fuzzy Matches with the Power Query Editor by Chris Menard

Power Query in Excel for Office 365 performs fuzzy matches. Imagine you need to compare two lists of customers. One list is the master list, and the 2nd list is also a customer list, but not an exact match. For example, the master list has "The Home Depot" and the second list shows "Home Depot." So the customer names are close but not an exact match. Fuzzy matches to the rescue! With fuzzy matches, you can match approximates. This works on other types of list: products, account codes, descriptions, employee names, etc.

I can't use VLOOKUP or the new function, XLOOKUP, since there isn't an exact match. There is also no pattern, so XLOOKUP and VLOOKUP are out, but fuzzy matches in Power Query will handle the job. 

The screenshot below shows the master list in column A and the 2nd list in column C.

Create a query for both Tables

  1. Select a cell inside the list and convert it to a table. CTRL + T is the keyboard shortcut. With the mouse, click the Home Tab - click Format as Table in the Styles group.
  2. Go to the Data tab.
  3. Use the From Table/Range query command.
  4. Go to the Home tab in the Power Query editor.
  5. Select Close & Load ➜ Close & Load To.
  6. Choose Only Create Connection from the Import Data menu. Click OK.

Do the exact same steps for the second list. You can also make both ranges a table to start and then make both tables a connection only. 

Note: This is in the video from 01:39 to 2:30

Merge Queries

Steps to Create a Merge query

  1. Click the Data tab
  2. Choose Get Data from the ribbon
  3. Choose Combine Queries from the menu
  4. Choose Merge

 

 

Fuzzy Matching window

Select your two tables and click Use fuzzy matching to perform the merge. In the example below, I accepted all the defaults.

Fuzzy Matching Options

Expanding Fuzzy matching options reveals the advaned features of funny matching. This is usually necessary to get better matching.

Similarity threshold (optional)

This is a range from 0 to 1. The default is 0.80. One means only matches would be found. Zero means everything would match. In my video, I used 0.5 to get all the matches. 

Ignore Case

Power Query is case sensitive. That means R and r are not the same. Ignore Case would ignore Chris Menard in one table and CHRIS MENARD in another table

Match by combining text parts

With this box check the words "web page" and "webpage" would be matched.

 

Expand Merged Data In The Power Query Editor

  1. Click to Expand the 2nd Table (table2 in my example)
  2. Click OK
  3. Close and Load - Load to a New Worksheek as a Table.

test

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories