Power Query Problems

How to Compare Names in Two Datasets using Fuzzy Lookup in Power Query

Rana Garcia Sep 25, 2026 874 views

Question details

The user needs to compare and match names with slight text variations across two separate datasets using a fuzzy matching approach.

How to Compare Names in Two Datasets with a Fuzzy Lookup

Product: Microsoft Excel
Device & OS: not provided
Scenario: Merging two datasets where the primary text identifiers (names) contain minor discrepancies like typos or differing formats, but correspond to the same entity.
Observed behavior: Standard exact matching fails to align slightly different names. A fuzzy match is required to link the records while safely retaining the original ID1 and ID2 columns for data integrity.

Before you start

Ensure both of your datasets are formatted as Tables before importing them into Power Query, and verify that your target name columns do not contain excessive leading or trailing spaces.

Solution 1Recommended

Use Merge Queries with Fuzzy Matching in Power Query

The most effective method to compare slightly differing names is to use the Merge Queries feature with fuzzy matching enabled, allowing you to align data while retaining original IDs.

Fuzzy matching relies on a specialized algorithm to identify text similarity rather than requiring an exact character-for-character match. This is highly useful for consolidating user-entered data.

Load Tables into Power Query

Select any cell in your first dataset, navigate to the Data tab, and click 'From Table/Range' to load it into the Power Query Editor. Repeat this process for your second dataset.

Initiate Merge Queries

In the Power Query Editor, ensure your primary table is selected. Go to the Home tab, click the 'Merge Queries' dropdown, and choose 'Merge Queries'.

Enable Fuzzy Matching

In the Merge dialog, select your second table from the dropdown. Click on the Name columns in both tables to highlight them. Check the box labeled 'Use fuzzy matching to perform the merge'.

Expand and Retain Original IDs

Once the merge completes, click the expand icon in the header of the newly merged column. Select the Name column, along with ID1 and ID2, to ensure the original records remain perfectly aligned.

Use Merge Queries with Fuzzy Matching in Power Query

Data Integrity Preserved: By intentionally selecting ID1 and ID2 during the expansion step, you ensure that no underlying reference data is lost, even if the names are heavily modified by the fuzzy merge.

Solution 2

Enable Similarity Scores to Review Match Quality

If the fuzzy merge produces unexpected or overly aggressive matches, you can enable similarity scores to review how closely the names correspond.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

While Power Query is a specific Microsoft feature, WPS Office provides a lightweight, free alternative for advanced data analysis. It offers complete format compatibility, standard lookup functions, and a familiar user interface for all your spreadsheet tasks.

1. Download WPS Office: Navigate to the official WPS website and download the free software suite.
2. Install and Launch: Follow the quick installation steps and launch the WPS Spreadsheets application.
3. Open Your Workbooks: Directly open your existing .xlsx files in WPS Office without losing any of your original data formatting.

Free, lightweight, and fast data processingFully compatible with Microsoft Excel (.xlsx) formatsFamiliar interface with a zero learning curveBuilt-in data comparison and robust lookup functions

microsoft office alternative - wps office

Frequently Asked Questions

Why is the fuzzy merge not finding any matches?

If names are too distinct or the default similarity threshold is too strict, Power Query will not consider them a match. Try expanding the 'Fuzzy matching options' and lowering the similarity threshold.

How do I keep my original ID columns after merging?

When you click the double-arrow icon to expand the merged table column in Power Query, ensure you manually check the boxes for both ID1 and ID2 so they are included alongside the newly matched names.

What does a similarity score mean in Power Query?

A similarity score is a generated value between 0.00 and 1.00 that indicates how closely two text strings match. A score of 1.00 means an exact identical match, while lower scores indicate partial matches based on typos or abbreviations.

Can I ignore case when performing a fuzzy match?

Yes. Inside the fuzzy matching options dialog, there is an 'Ignore case' checkbox. Leaving this checked treats uppercase and lowercase letters identically during the name comparison.

Excel & Data Problems