Fuzzy match support for Get & Transform (Power Query)

Power Query's Merge Queries feature supports approximate string comparison logic (fuzzy matching) when trying to find matches across joining table columns. Fuzzy matching lets you compare items in separate lists and join them if they're close to each other. You can set the matching tolerance, called the Similarity Threshold, or let Power Query do it for you.

Get started with fuzzy matching

Note: The following instructions assume you already have tables loaded into Power Query. If you haven't, then please see: Getting started with Power Query.

  1. To create a fuzzy match, select your primary table, then go to Query > Edit.

  2. In the Power Query Editor, go to Home > Combine > Merge Queries. Choose either Merge Queries, or Merge Queries as New.

  3. Your primary table will appear at the top of the Merge dialog, so select the column you want to use for your fuzzy match. In the following example, we selected the First Name field.

  4. Next, select the secondary table to be joined from the drop-down menu, then select the matching column. Again, we selected the First Name field.

  5. Check the Use fuzzy matching to perform the merge option. Power Query will analyze your tables, and display a notice about how many matches it was able to make. If you're satisfied, then you can press OK to continue. Otherwise, click the Fuzzy merge options arrow to customize your experience.

Power Query fuzzy merge options

Fuzzy merge options

  1. Similarity Threshold – This option indicates how similar two values need to be in order to match. The minimum value of 0.00 will cause all values to match each other, and the maximum value of 1.00 will only allow exact matches. The default is 0.80.

  2. Ignore case – This option indicates whether text values should be compared in a case sensitive or insensitive setting. The default behavior is case insensitive, which ignores casing.

  3. Maximum number of matches – This option controls the maximum number of matching rows that will be returned for each input row. For example, if you only want to find one matching row for each input row, specify a value of 1. The default behavior is to return all matches.

  4. Transformation table – This option allows users to specify another query that holds a mapping table, so that some values can be auto-mapped as part of the matching logic. For example, defining a two-column table with a “From” and “To” text columns with values “Microsoft” and “MSFT” will make these two values be considered the same (similarity score of 1.00) by the matching logic.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Getting started with Power Query

Merge queries (Power Query)

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.