How to Use Fuzzy Matching in Power Query to Fix Spelling Errors

Most data cleaning problems have the same root cause: the same entity spelled seventeen different ways. "Microsoft Corp," "Microsft Corporation," "MICROSOFT," "Micro soft" — all the exact same company, yet impossible to group, aggregate, or join on without intervention. Manually correcting thousands of rows in Excel is slow, fragile, and breaks every time a new monthly export arrives.

Fuzzy matching fixes this at the query level, automatically, in a repeatable pipeline that refreshes with your data. This guide covers how to configure it, how to tune the similarity threshold, how to build Transformation Tables for edge cases, and how to write production-grade M code.

What Fuzzy Matching Actually Does

Standard merges in Power Query (and in every standard SQL INNER JOIN or LEFT JOIN) operate strictly on exact equality. "Microsoft Corp" = "Microsoft Corp" matches. But "Microsoft Corp" ≠ "Microsft Corporation" — even though a human analyst instantly recognizes they are the same company. Exact matching has zero tolerance for real-world data entry anomalies.

Fuzzy matching calculates a mathematical similarity score between two text strings — a floating-point number between 0.00 and 1.00 — and treats them as a valid match if that score meets or exceeds your specified threshold. For example, "Microsoft Corp" and "Microsft Corporation" might score around 0.78. If your threshold is set to 0.75, they match. If your threshold is set to 0.85, they fail to match.

The algorithm Power Query utilizes is built upon Jaccard similarity combined with tokenization and character n-gram distance techniques. Strings that share more of the same character subsequences score higher. Crucially, shorter strings with single-character differences score lower than longer strings with the same single-character difference, because the missing letter represents a larger proportion of the total text.

Understanding this mechanic helps you tune thresholds intelligently:

• Long strings with minor typos: A one-letter omission in "Samsng Electronics" (missing 'u') scores around 0.93 because the total string is long and almost all character sequences overlap.
• Short strings or abbreviations: A heavily abbreviated ticker like "MSFT" compared to "Microsoft Corp" scores below 0.30 because almost no letter clusters match. This is exactly where a Transformation Table takes over from mathematical similarity scoring.

The Sample Data Used Throughout This Guide

Every example throughout this tutorial uses a consistent Orders table and Lookup table. The Orders table contains ten rows with intentionally messy corporate names — the typical variations generated by manual data entry, Salesforce text fields, and regional accounting exports.

Orders Table (Messy Raw Input — 10 Rows):

Lookup Table (Canonical Dimension — 3 Rows):

The goal: Add a clean CompanyName_Clean column to the Orders table by matching each raw variant against the Lookup table, enabling reliable star-schema relationships and slicers. Without fuzzy matching, standard exact merging fails on 7 out of 10 rows (70% data loss).

Method 1: Fuzzy Merge (Matching Across Two Tables)

Fuzzy Merge is the primary, production-grade technique. It joins two queries while scoring character similarity across key columns.

Step 1: Load Both Tables Into Power Query

In Power BI Desktop, click Home → Transform Data to launch the Power Query Editor. Import both your fact table (Orders) and your dimension/lookup table (Lookup). Both queries will appear in the left-hand Queries pane.

Step 2: Start the Merge From the Orders Query

Select the Orders query in the left pane. In the Home ribbon, click Merge Queries (or select Merge Queries as New if you want to keep the raw fact query isolated).

Step 3: Configure the Fuzzy Merge Keys

In the Merge dialog:

• Top table: Click the CompanyName_Raw column header in the Orders preview.
• Bottom table: Select Lookup from the dropdown, then click CompanyName_Clean.
• Join Kind: Select Left Outer (all from first, matching from second). This guarantees no sales rows are lost, even if a name fails to match.
• Enable Fuzzy Matching: Check the box labeled Use fuzzy matching to perform the merge.

Step 4: Configure the Fuzzy Matching Options

Expand the Fuzzy matching options section. Five controls determine how lenient or strict Power Query will be:

Step 5: Click OK and Review the Result

Click OK. Power Query performs the fuzzy join and appends a new column containing nested Table objects for each row in Orders.

Step 6: Expand the Matched Column

Click the Expand icon (↔) at the top right of the new column header. Select CompanyName_Clean, uncheck "Use original column name as prefix", and click OK. The clean company names now appear directly alongside your sales metrics.

Step 7: Handle Unmatched Rows (Nulls)

Filter the CompanyName_Clean column for null. Any null row represents a text string that failed to reach your threshold. For production queries, do not leave these unhandled:

• If legitimate variations are missing, lower the threshold slightly (e.g., from 0.80 to 0.75).
• If they are abbreviations (like "MSFT"), feed them into a Transformation Table (covered in Method 2).
• Create an exception branch in Power Query that routes nulls to an "Unmatched Exceptions" table for data governance review.

Step 8: Rename and Clean Up

Remove the original messy column (or rename it to CompanyName_Original for auditing), verify numeric data types for Revenue, and click Home → Close & Apply.

Method 2: The Transformation Table — Making Fuzzy Matching Production-Ready

Algorithmic similarity scoring excels at typos and minor formatting flaws. However, it completely fails on domain abbreviations, acronyms, and structural renames. For example, "MSFT" and "Microsoft Corp" share almost zero character n-grams. Lowering the similarity threshold down to 0.25 to catch "MSFT" would cause catastrophic false positives across your entire dataset.

A Transformation Table provides a deterministic safety net. It allows you to specify explicit from-to mapping overrides that Power Query applies prior to or in conjunction with similarity evaluation.

What a Transformation Table Looks Like

Create a separate query in Power Query named TransformationTable. It must contain exactly two columns named From and To:

How to Add the Transformation Table to Your Fuzzy Merge

Re-open your Fuzzy Merge step by clicking the gear icon beside Merged Queries in the Applied Steps list. In the Transformation table dropdown, select your TransformationTable query and click OK.

Method 3: Cluster Values (Grouping Within a Single Column)

While Fuzzy Merge joins two separate queries, Cluster Values operates internally on a single column within a single table. It analyzes string proximity and automatically replaces variations with a single representative cluster value.

Step 1: Select the Column to Cluster

Click the header of your messy column (e.g. CompanyName_Raw) in the Power Query data preview.

Step 2: Open Cluster Values

In the ribbon, navigate to Transform → Cluster Values (note: this is in the Transform tab, not the Home tab).

Step 3: Configure Threshold and Destination

Enter a new column name (such as CompanyName_Clustered) and set your similarity threshold. Power Query groups the similar rows and replaces variations with the cluster representative.

The M Code Behind Fuzzy Matching

When you configure fuzzy joins through the UI, Power Query generates the Table.FuzzyNestedJoin or Table.FuzzyJoin M functions. Opening the Advanced Editor reveals powerful options not exposed in the visual dialog.

Fuzzy Merge M Code with Match Scoring

let
    Source = Orders,
    #"Merged Queries" = Table.FuzzyNestedJoin(
        Source,
        {"CompanyName_Raw"},
        Lookup,
        {"CompanyName_Clean"},
        "Lookup",
        JoinKind.LeftOuter,
        [
            IgnoreCase = true,
            IgnoreSpace = true,
            SimilarityColumnName = "MatchScore",
            Threshold = 0.75,
            TransformationTable = TransformationTable,
            MaxMatches = 1
        ]
    ),
    #"Expanded Lookup" = Table.ExpandTableColumn(
        #"Merged Queries",
        "Lookup",
        {"CompanyName_Clean", "MatchScore"},
        {"CompanyName_Clean", "MatchScore"}
    )
in
    #"Expanded Lookup"

Secret Feature: SimilarityColumnName
The Power Query UI does not provide a checkbox to show the numeric similarity score. By inserting SimilarityColumnName = "MatchScore" into the options record in M code, Power Query outputs a decimal score (e.g. 0.93, 0.78) into each nested table. When expanded, you can audit the exact confidence score for every row in your data model!

Fuzzy Group (Cluster Values) M Code

= Table.FuzzyGroup(
    Source,
    "CompanyName_Raw",
    {{"CompanyName_Clustered", each _, type table}},
    [
        IgnoreCase = true,
        IgnoreSpace = true,
        Threshold = 0.80,
        TransformationTable = TransformationTable
    ]
)

Tuning the Threshold: A Practical Decision Guide

Selecting the threshold requires balancing false negatives (failing to match real variations) against false positives (accidentally merging different entities). Use this benchmark guide:

In Practice: A Real Cleaning Scenario

A retail analytics team receives weekly sales exports from three regional ERP systems. Each system stores client names differently — one uses stock abbreviations, one records full legal titles, and the third relies on open text entry. Joining these systems into a unified Star Schema failed on 65% of records with standard joins.

The team implemented an automated Power Query pipeline:

1. Combined regional workbooks using the Folder connector into an Orders_Combined query.
2. Maintained a centralized Lookup_Customers Excel table on SharePoint containing canonical IDs and clean names.
3. Configured a TransformationTable containing 32 recurring company abbreviations (e.g. "GE", "IBM", "P&G").
4. Executed a Table.FuzzyNestedJoin at threshold 0.75 with SimilarityColumnName = "MatchScore".
5. Routed rows into three audit buckets:
   • Score ≥ 0.90: Auto-approved high confidence.
   • Score 0.75 – 0.89: Loaded with a "Fuzzy Matched" flag for analyst visibility.
   • Score null: Filtered to a lightweight "Unmatched_Exceptions" query that alerts the data steward on weekly refresh.

Over six months of operations, new typos were resolved automatically, while brand new abbreviations were simply appended to the SharePoint Transformation Table without modifying report queries. The unmapped exception rate plummeted from 65% to under 0.4%.

Common Mistakes and How to Avoid Them

Frequently Asked Questions