How to Perform Fuzzy Matching and Merge Approximate Text in Microsoft Excel Using Power Query

Data analysts and business professionals frequently encounter disparate datasets where key identifiers—such as customer names, vendor labels, or product titles—do not match identically across tables due to typos, varying punctuation, abbreviations, or inconsistent casing. Standard Excel lookup functions such as XLOOKUP, VLOOKUP, and INDEX/MATCH require exact character-for-character equivalence (or basic wildcards), causing reconciliation workflows to break down. Microsoft Excel’s Power Query engine provides native Fuzzy Matching capabilities through the Table.FuzzyNestedJoin M language function. By configuring similarity thresholds, case sensitivity, and transformation mapping tables, analysts can automate approximate record linking without writing complex VBA scripts or external Python routines.

Understanding How Fuzzy Matching Works in Power Query

Power Query evaluates approximate text equivalence by computing similarity scores between zero and one (where 1.0 represents an exact match and 0.0 represents zero resemblance). The matching engine uses Jaccard similarity and character n-gram tokenisation to compare string fragments. When merging two tables with fuzzy matching, Power Query considers several operational parameters:

• Similarity Threshold: A numerical value between 0.00 and 1.00 that establishes the minimum match confidence required to pair records. The default threshold is 0.80, which accommodates typical typographical slips and slight variations.
• Ignore Case: Disregards uppercase and lowercase distinctions during comparison.
• Match by Combining Text Parts: Tokenises compound words or concatenated strings (e.g. comparing “Micro Soft” with “Microsoft”).
• Maximum Number of Matches: Restricts the number of candidate records returned for each source row (default is 1 to return the best match).
• Transformation Table: An optional user-defined reference table that explicitly maps known business abbreviations or domain-specific aliases (such as mapping “MSFT” to “Microsoft Corp”) before the mathematical similarity calculation runs.

Preparing Data and Loading Tables into Power Query

Before executing a fuzzy merge, format both dataset ranges as official Excel Tables:

1. Select your first dataset (e.g. Sales Invoices) and press Ctrl + T to convert it to an Excel Table. In the Table Design tab, name it InvoicesTable.
2. Select your reference dataset (e.g. Master Customer Registry) and press Ctrl + T. Name it CustomersMaster.
3. Navigate to the Data ribbon tab and click From Sheet (or From Table/Range) to load both tables into the Power Query Editor.
4. In Power Query, select Close & Load To… and choose Only Create Connection for both queries.

Configuring the Fuzzy Merge Interface in Excel

To merge the two tables based on approximate string comparison:

1. In Excel, go to Data > Get Data > Combine Queries > Merge.
2. In the Merge dialog, select InvoicesTable in the top dropdown and CustomersMaster in the lower dropdown.
3. Click the target columns to join (e.g. ClientName in both tables).
4. Select the Left Outer join kind to preserve all invoice rows while retrieving matching master customer records.
5. Check the box labelled Use fuzzy matching to perform the merge.
6. Expand the Fuzzy matching options section to reveal granular tuning parameters.

Fine-Tuning Similarity Thresholds and Tokenisation

Adjust the fuzzy options to optimize match precision and avoid false positives:

• Similarity threshold: Enter 0.75 or 0.80. A threshold that is too low (e.g. 0.50) risks pairing unrelated entries with similar short vowel patterns; a threshold set to 0.95 will reject common spelling variations.
• Ignore case: Keep this checked (enabled by default).
• Match by combining text parts: Check this option if your text frequently contains arbitrary spaces (e.g. “Data base” vs “Database”).
• Maximum number of matches: Leave as 1 to guarantee a 1:1 join structure and avoid row multiplication.

Constructing and Applying a Transformation Table

Certain business acronyms or legal designations cannot be resolved by character similarity alone—for instance, “GE” vs “General Electric”, or “Corp” vs “Inc”. A Transformation Table bridges this semantic gap.

To create a transformation mapping:

1. In a new Excel worksheet, create a two-column table with the exact headers From and To.
2. Populate the table with mappings:

   From    | To
   MSFT    | Microsoft
   GE      | General Electric
   IBM Corp| International Business Machines

3. Convert the range to a table (Ctrl + T) and name it AliasMapping. Load it into Power Query as a connection.
4. In the Merge dialog’s Fuzzy matching options, select AliasMapping under the Transformation table dropdown.

Reviewing the Generated M Code and Expanding Merged Columns

When you confirm the merge, Power Query generates a step using Table.FuzzyNestedJoin. In the Power Query Advanced Editor, the underlying M code appears as follows:

let
    Source = Table.FuzzyNestedJoin(
        InvoicesTable,
        {"ClientName"},
        CustomersMaster,
        {"CustomerName"},
        "MatchedCustomer",
        JoinKind.LeftOuter,
        [
            IgnoreCase = true,
            IgnoreSpaces = true,
            Threshold = 0.8,
            TransformationTable = AliasMapping,
            NumberOfMatches = 1
        ]
    ),
    #"Expanded MatchedCustomer" = Table.ExpandTableColumn(
        Source,
        "MatchedCustomer",
        {"CustomerID", "OfficialAccountName", "CreditLimit"},
        {"Master_ID", "Master_Name", "Credit_Limit"}
    )
in
    #"Expanded MatchedCustomer"

Auditing and Validating Fuzzy Match Results

After expanding the merged records:

1. Filter the expanded Master_ID column to identify rows that returned null, indicating records where similarity fell below the configured threshold.
2. Compare ClientName side by side with Master_Name to audit for false positive pairings.
3. If legitimate matches were missed, either lower the threshold incrementally (e.g. from 0.80 to 0.75) or add specific alias entries to your AliasMapping table.
4. Click Close & Load to output the unified, reconciled dataset back to an Excel worksheet.

Leveraging Power Query’s fuzzy merge engine transforms tedious manual reconciliation into an automated, repeatable data cleaning pipeline that updates instantly whenever source data is refreshed.