• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Fuzzy Matching in Power Query

You are here: Home / Power Query / Fuzzy Matching in Power Query
fuzzy matching in power query
June 10, 2021 by Philip Treacy

Fuzzy matching is the ability to match non-identical text based on how similar one string is compared to the other.

You may have text that has been entered as the answer to a survey question, in which case you can't control what the respondent types.

This can result in misspelling of words, for example, take this table of companies (named Table1) and their products

sample table of data

It's clear to you and I that Msoft, MICROSOFT. and Micro Soft mean the same company based in Redmond, Washington.

But if we want to clean up that column and replace all the different versions of Microsoft with a single version Microsoft you can't do it with a regular match (query merge) in Power Query. Fuzzy matching to the rescue.

Fuzzy Matching Availability

Fuzzy matching is only available in Excel in Microsoft 365, and in Power BI.

Watch the Video

Subscribe YouTube

 

Download Sample Excel Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

 

If I have a second table (named Table2) consisting of the companies names as I want them displayed

table of company names

I can load both tables into Power Query as connection only queries.

From within the Power Query Advanced Editor,select either query and then from the Ribbon choose Merge Queries -> Merge Queries as New

merge queries as new

In the Merge dialog window select Table1 as the first table and Table2 as the second table. Leave Join Kind as Left Outer.

Click on the Company column in both tables to make the join on those columns.

merge query dialogYou can see with these settings that only 3 out of 14 rows match, because as it is, PQ is doing an exact match.

Check Use fuzzy matching to perform the merge and you can now see that 10 rows match. The fuzzy matching algorithm has been able to identify a further 7 matches.

check fuzzy matching box

Click on Fuzzy matching options to see what options are available to fine tune the matching. You'll need to use the scroll bar to see all the options. Why can't they make the window bigger?

fuzzy matching options

The first thing you'll see is the Simlilarity threshold which has a default value of 0.8.

A value of 1.0 means only look for exact matches. As you lower the value, the fuzzy matching algorithm gets less strict with how it matches text strings.

If I make the value 0.7, you can see that there are now 11 matches.

reduce fuzzy matching threshold

Reducing the value further, in this case, doesn't make any more matches. But depending on your own situation, you may find that reducing this threshold value gives you more matches.

Just be careful that you don't reduce it too low or you'll end up with incorrect matches because the algorithm has become very 'relaxed' with matching up strings.

For now, let's click OK to close the Merge dialog and see what our results are.

The merge gives me another column with a table in it

merged queries table

Clicking the icon in the new column header to expand these tables gives this

expand table column

Rows 1, 9 and 12 don't have a match. Lets open the Source step to adjust the Similarity Threshold and see if that helps.

open source step

Gradually lowering the threshold by 0.1 each time, even going all the way down to 0.1 doesn't give me any more matches. Looks like I'm going to have to use a Transformation Table.

lower fuzzy similarlity threshold

A Transformation Table is a table with 2 columns (From and To) that explicity states what the string in the From column will be changed To.

To create a Transformation Table, I go back into Excel and create this table that I named Transform.

transformation table

It lists the three companies in Table1 that don't have a match, and specifies in the To column what to use when the query merge is performed.

Load this table into Power Query (connection only).

In the Merge1 query, open the Source step again. In the Fuzzy Matching Options, scroll to the bottom until you see the Transformation table (optional) section.

Click on the drop down and select the Transform table.

select transformation table

The merge now tells me that it can match all 14 rows

all matches complete

With all rows matched in my merge I can get rid of the first column of incomplete or messed up company names, and end up with this nice table.

final fuzzy merged table

fuzzy matching in power query

More Power BI Posts

combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

Clearly communicate issues with custom messages when dealing with web scraping or API server errors. Download sample Excel and Power BI files
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
sort by column in power bi

Sort-By Columns in Power BI

Create a Sort-By column to allow custom sort order in your Power BI Visuals. Download an example Power BI Desktop file
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power BI
Previous Post:QATExcel Quick Access Toolbar
Next Post:Show Items with no Data in PivotTables

Reader Interactions

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.