Implementing Fuzzy Matching and Probabilistic Record Linkage in Power Query: Deduplicating and Joining Imperfect Data at Scale

Introduction

Your customer database has 847,000 rows. Your marketing team collected leads from three different campaign tools. Your ERP migrated data from a legacy system that used a different address format. Now you need to deduplicate customers, join purchase histories to the right people, and produce a single clean view — but "Robert Smith" in one system is "Bob Smith" in another, "123 Main St" became "123 Main Street," and "Acme Corp." is stored as "ACME Corporation" somewhere else. Exact matching won't work. You need a smarter approach.

This is one of the most common and most underappreciated problems in data engineering. Fuzzy matching and probabilistic record linkage are the techniques that solve it — but most Power Query practitioners either don't know they exist at a native level or, worse, apply them naively and produce garbage results at scale. Power Query's built-in fuzzy matching features are genuinely powerful, but they behave differently than most people expect, and the default settings will quietly fail you in production if you don't understand what's happening under the hood.

By the end of this lesson, you'll understand how Power Query's fuzzy matching engine works internally, how to tune its threshold and transformation parameters for different data quality scenarios, how to build a probabilistic scoring layer on top of it using M code, how to handle large datasets without timing out, and how to validate your matching results so you can trust them.

What you'll learn:

• How Power Query implements fuzzy matching using Jaccard similarity over character n-grams, and why that matters for tuning
• How to use Table.Fuzzy* functions with every parameter explained and reasoned through
• How to build a multi-field probabilistic record linkage pipeline using weighted field scores
• How to structure blocking strategies to make fuzzy joins performant on large datasets
• How to validate match quality with precision/recall analysis inside Power Query itself

---

Prerequisites

This lesson assumes you're comfortable writing M code directly in the Advanced Editor — not just clicking through the UI. You should understand how Table.Join, List.Generate, and custom functions work. Familiarity with how Power Query evaluates lazily (and when it doesn't) will help you understand the performance sections. If you've never written a recursive M function or used Table.Buffer, you may want to revisit the intermediate M programming lessons first.

---

How Power Query's Fuzzy Matching Actually Works

Before you touch a single parameter, you need a mental model of what's happening inside the engine. Power Query's fuzzy matching is not based on edit distance (Levenshtein). This surprises most people because Levenshtein is the most commonly discussed string similarity algorithm. Instead, Power Query uses token-based Jaccard similarity over character n-grams.

Here's what that means in plain terms. Given the string "Robert Smith", the engine tokenizes it by generating a set of overlapping character sequences — typically trigrams (3-character substrings). So "Robert" becomes the set: {rob, obe, ber, ert}. "Roberta" becomes {rob, obe, ber, ert, rta}. The Jaccard similarity is the size of the intersection divided by the size of the union:

Jaccard("Robert", "Roberta") = |{rob, obe, ber, ert}| / |{rob, obe, ber, ert, rta}| = 4/5 = 0.80

This has important implications:

Short strings perform poorly. A string like "Al" produces very few trigrams — in fact, none at all since trigrams require at least 3 characters. Power Query falls back to bigrams for short strings, but two-character strings produce a single bigram and the similarity calculations become unreliable. If you have fields like state codes ("CA", "NY"), fuzzy matching on them will behave erratically.

Token order matters less than you might think. Because this is set-based, "Smith, Robert" and "Robert Smith" share all the same trigrams. The engine handles name reversal reasonably well without you needing to normalize it first. However, transposed words in longer strings can still create gaps.

Frequency effects are real. Common substrings like "ing" or "the" appear in many values. The Jaccard calculation doesn't weight rare n-grams more heavily the way TF-IDF would. This means two entirely different company names that both contain "International" will score higher similarity than you'd want.

Understanding this tells you something critical: preprocessing your strings before fuzzy matching is not optional, it's the single highest-leverage thing you can do. Normalizing case, removing punctuation, expanding abbreviations, and stripping stop words all directly improve the n-gram overlap signal.

---

The `Table.FuzzyJoin` and `Table.FuzzyGroup` Functions

Power Query exposes fuzzy capabilities through two primary functions: Table.FuzzyJoin and Table.FuzzyGroup. Let's examine each with full parameter breakdowns.

`Table.FuzzyJoin`

The signature is:

Table.FuzzyJoin(
    table1 as table,
    key1 as any,
    table2 as table,
    key2 as any,
    joinKind as JoinKind.Type,
    [fuzzyJoinOptions as nullable record]
) as table

The fuzzyJoinOptions record is where all the tuning lives. Here are all the available options:

[
    Threshold = 0.8,               // Minimum similarity score to consider a match (0.0–1.0)
    NumberOfMatches = 1,           // How many matches to return per left-side row
    IgnoreCase = true,             // Case-insensitive comparison
    IgnoreSpace = true,            // Normalize whitespace before comparing
    SimilarityColumnName = null,   // If set, adds a column with the actual score
    TransformationTable = null     // A two-column table mapping values to their canonical forms
]

A realistic example: you're joining a customer master list to a CRM export, matching on company name.

let
    // Customer master from ERP
    CustomerMaster = Table.FromRecords({
        [CustomerID = "C001", CompanyName = "Acme Corporation", State = "CA"],
        [CustomerID = "C002", CompanyName = "Global Dynamics LLC", State = "NY"],
        [CustomerID = "C003", CompanyName = "Pinnacle Solutions Inc", State = "TX"],
        [CustomerID = "C004", CompanyName = "Blue Ridge Manufacturing", State = "NC"]
    }),

    // CRM leads with messy company names
    CRMLeads = Table.FromRecords({
        [LeadID = "L001", Company = "ACME Corp.", SalesRep = "Jennifer"],
        [LeadID = "L002", Company = "Global Dynamics", SalesRep = "Marcus"],
        [LeadID = "L003", Company = "Pinnacle Solution", SalesRep = "Jennifer"],
        [LeadID = "L004", Company = "Blue Ridge Mfg", SalesRep = "Priya"],
        [LeadID = "L005", Company = "Acme Corp", SalesRep = "Marcus"]
    }),

    // Fuzzy join on company name
    FuzzyJoined = Table.FuzzyJoin(
        CRMLeads,
        "Company",
        CustomerMaster,
        "CompanyName",
        JoinKind.LeftOuter,
        [
            Threshold = 0.6,
            NumberOfMatches = 1,
            IgnoreCase = true,
            IgnoreSpace = true,
            SimilarityColumnName = "MatchScore"
        ]
    ),

    // Expand the matched columns
    Expanded = Table.ExpandTableColumn(
        FuzzyJoined,
        "CustomerMaster",
        {"CustomerID", "CompanyName", "State"},
        {"Matched_CustomerID", "Matched_CompanyName", "Matched_State"}
    )
in
    Expanded

Notice the Threshold = 0.6. That's lower than many people use. "Blue Ridge Mfg" vs "Blue Ridge Manufacturing" actually scores around 0.62 due to the n-gram difference introduced by the abbreviation. If you set the threshold to 0.8 (a common default), you'd miss that match entirely.

Warning: Setting SimilarityColumnName causes the engine to materialize the similarity scores for every candidate pair it evaluates, not just the final matches. On large tables, this adds memory pressure. Use it during tuning but consider removing it in production queries.

The `TransformationTable` Parameter

This is the most underused and most powerful option in the entire fuzzy join feature. A transformation table is a two-column table with columns From and To that maps variant forms to canonical forms before comparison. It's applied to both sides of the join.

let
    Abbreviations = Table.FromRecords({
        [From = "corp", To = "corporation"],
        [From = "corp.", To = "corporation"],
        [From = "inc", To = "incorporated"],
        [From = "inc.", To = "incorporated"],
        [From = "llc", To = ""],
        [From = "ltd", To = "limited"],
        [From = "mfg", To = "manufacturing"],
        [From = "intl", To = "international"],
        [From = "svcs", To = "services"],
        [From = "mgmt", To = "management"]
    }),

    FuzzyJoined = Table.FuzzyJoin(
        CRMLeads,
        "Company",
        CustomerMaster,
        "CompanyName",
        JoinKind.LeftOuter,
        [
            Threshold = 0.75,
            NumberOfMatches = 1,
            IgnoreCase = true,
            IgnoreSpace = true,
            TransformationTable = Abbreviations
        ]
    )
in
    FuzzyJoined

The transformation table applies substitution before the n-gram tokenization happens. This means it dramatically improves recall for known abbreviation patterns without you having to lower the threshold (which would increase false positives). Think of the transformation table as your domain-specific pre-processor. Build one that reflects your actual data — company suffix variants, address abbreviations (St → Street, Ave → Avenue), name shortenings — and you'll get substantially better results than threshold-tuning alone.

`Table.FuzzyGroup`

Table.FuzzyGroup works like Table.Group but uses fuzzy matching to group rows with similar key values together. It's your primary tool for deduplication.

Table.FuzzyGroup(
    table as table,
    key as any,
    aggregatedColumns as list,
    [fuzzyGroupOptions as nullable record]
) as table

The fuzzyGroupOptions accept the same Threshold, IgnoreCase, IgnoreSpace, and TransformationTable options. There is no SimilarityColumnName here — you get groups, not scores.

Here's a realistic deduplication scenario. You have a vendor list that came from multiple system imports and you want to find all the duplicate entries so you can assign a canonical vendor ID.

let
    RawVendors = Table.FromRecords({
        [VendorID = "V001", Name = "Westbrook Consulting Group", City = "Chicago"],
        [VendorID = "V002", Name = "Westbrook Consulting Grp.", City = "Chicago"],
        [VendorID = "V003", Name = "WESTBROOK CONSULTING GROUP", City = "Chicago"],
        [VendorID = "V004", Name = "Pacific Rim Technology Partners", City = "Seattle"],
        [VendorID = "V005", Name = "Pacific Rim Tech Partners", City = "Seattle"],
        [VendorID = "V006", Name = "Harrison & Webb Associates", City = "Boston"],
        [VendorID = "V007", Name = "Harrison and Webb Associates", City = "Boston"]
    }),

    // Group fuzzy duplicates — collect all VendorIDs in each cluster
    Grouped = Table.FuzzyGroup(
        RawVendors,
        "Name",
        {
            {"AllVendorIDs", each Text.Combine([VendorID], ", "), type text},
            {"RowCount", Table.RowCount, Int64.Type},
            {"CanonicalName", each List.First([Name]), type text}
        },
        [
            Threshold = 0.75,
            IgnoreCase = true,
            IgnoreSpace = true
        ]
    )
in
    Grouped

This groups V001, V002, and V003 together (all variants of Westbrook), giving you a cluster you can review. The CanonicalName picks the first value in the group — you'd typically want to replace this with a function that picks the longest, the one from the authoritative system, or the one that matches a master list.

Important limitation: Table.FuzzyGroup uses a greedy clustering algorithm. It doesn't guarantee globally optimal clusters. If record A is similar to B, and B is similar to C, but A and C don't score above threshold, A and C may or may not end up in the same group depending on the order Power Query processes them. For high-stakes deduplication, treat the output as a candidate set for human review, not a final answer.

---

Building a Probabilistic Record Linkage Pipeline

The built-in fuzzy functions work on a single field. Real record linkage almost always requires matching on multiple fields simultaneously and combining those signals intelligently. This is where you move from "fuzzy matching" into proper probabilistic record linkage.

The classic framework here comes from Fellegi and Sunter (1969), and while we won't implement the full statistical model, the core intuition is: each matching field contributes evidence for or against a record pair being a true match, and you combine those pieces of evidence into an overall match score.

Step 1: Preprocessing Functions

Write a normalization function that you'll apply to all string fields before comparison.

let
    NormalizeString = (input as nullable text) as text =>
        let
            // Handle nulls
            Safe = if input = null then "" else input,
            // Lowercase
            Lower = Text.Lower(Safe),
            // Remove punctuation
            NoPunct = Text.Select(Lower, {"a".."z", "0".."9", " "}),
            // Normalize whitespace
            Trimmed = Text.Trim(NoPunct),
            Normalized = Text.Combine(
                List.Select(Text.Split(Trimmed, " "), each _ <> ""),
                " "
            )
        in
            Normalized
in
    NormalizeString

Step 2: A Jaccard Similarity Function

Since we're building a multi-field scorer, we need to call similarity calculations ourselves. Here's an M implementation of trigram-based Jaccard similarity that mirrors what the engine uses internally:

let
    JaccardTrigram = (str1 as text, str2 as text) as number =>
        let
            // Generate trigrams from a string
            Trigrams = (s as text) as list =>
                let
                    Len = Text.Length(s),
                    NGramSize = if Len < 3 then Len else 3,
                    Count = if Len < NGramSize then 0 else Len - NGramSize + 1,
                    Grams = List.Transform(
                        {0..Count - 1},
                        each Text.Middle(s, _, NGramSize)
                    )
                in
                    Grams,

            // Get trigram sets
            Set1 = List.Distinct(Trigrams(str1)),
            Set2 = List.Distinct(Trigrams(str2)),

            // Intersection and union
            Intersection = List.Intersect({Set1, Set2}),
            Union = List.Union({Set1, Set2}),

            // Handle edge case: both strings empty
            Score = if List.Count(Union) = 0
                    then 1.0
                    else List.Count(Intersection) / List.Count(Union)
        in
            Score
in
    JaccardTrigram

Performance note: This custom function is significantly slower than the engine's native implementation. Don't use it to join large tables row-by-row without a blocking strategy (covered later). It's appropriate for scoring candidate pairs that you've already narrowed down.

Step 3: The Multi-Field Scoring Function

Now build a function that takes two records and returns a composite similarity score. We'll use a weighted average where each field's weight reflects how discriminating it is:

let
    ScoreRecordPair = (rec1 as record, rec2 as record) as record =>
        let
            // Normalize all fields first
            Name1     = NormalizeString(Record.Field(rec1, "CompanyName")),
            Name2     = NormalizeString(Record.Field(rec2, "CompanyName")),
            City1     = NormalizeString(Record.Field(rec1, "City")),
            City2     = NormalizeString(Record.Field(rec2, "City")),
            Phone1    = Text.Select(Record.Field(rec1, "Phone") ?? "", {"0".."9"}),
            Phone2    = Text.Select(Record.Field(rec2, "Phone") ?? "", {"0".."9"}),
            Zip1      = Text.Start(Text.Select(Record.Field(rec1, "ZipCode") ?? "", {"0".."9"}), 5),
            Zip2      = Text.Start(Text.Select(Record.Field(rec2, "ZipCode") ?? "", {"0".."9"}), 5),

            // Calculate field-level scores
            NameScore  = JaccardTrigram(Name1, Name2),
            CityScore  = JaccardTrigram(City1, City2),
            PhoneScore = if Text.Length(Phone1) > 6 and Text.Length(Phone2) > 6
                         then (if Phone1 = Phone2 then 1.0 else 0.0)
                         else null,
            ZipScore   = if Text.Length(Zip1) = 5 and Text.Length(Zip2) = 5
                         then (if Zip1 = Zip2 then 1.0 else 0.0)
                         else null,

            // Weights (should sum to 1.0 for fields that exist)
            // Phone and Zip are exact-match fields — very high discriminating power
            Weights = [Name = 0.35, City = 0.15, Phone = 0.30, Zip = 0.20],

            // Compute weighted score, excluding null fields
            WeightedSum =
                Weights[Name]  * NameScore +
                Weights[City]  * CityScore +
                (if PhoneScore <> null then Weights[Phone] * PhoneScore else 0) +
                (if ZipScore   <> null then Weights[Zip]   * ZipScore   else 0),

            // Effective weight (only count weights of non-null fields)
            TotalWeight =
                Weights[Name] +
                Weights[City] +
                (if PhoneScore <> null then Weights[Phone] else 0) +
                (if ZipScore   <> null then Weights[Zip]   else 0),

            CompositeScore = if TotalWeight = 0 then 0 else WeightedSum / TotalWeight
        in
            [
                NameScore     = NameScore,
                CityScore     = CityScore,
                PhoneScore    = PhoneScore,
                ZipScore      = ZipScore,
                CompositeScore = CompositeScore
            ]
in
    ScoreRecordPair

The key design decision here is how you handle missing fields. If one record has no phone number, you don't want to penalize the match — you simply redistribute that weight across the fields that are present. The TotalWeight calculation handles this dynamically.

---

Blocking: The Strategy That Makes This Scale

Here's the hard truth: a naïve implementation of record linkage checks every possible pair. If you have 10,000 records in table A and 10,000 in table B, that's 100 million pairs. Even at 10,000 comparisons per second, that's 2.7 hours. Power Query will time out or crash long before that.

Blocking is the technique of restricting comparisons to pairs that share some exact-match key. You sacrifice some recall (you'll miss pairs where both records have a typo in the blocking key) in exchange for a tractable number of comparisons.

Choosing Blocking Keys

A good blocking key has high coverage (most records have it populated), meaningful selectivity (it narrows the comparison space significantly), and some tolerance for the kinds of errors in your data.

Common blocking strategies:

Phonetic blocking: Use Soundex or a simplified phonetic code of the first token of the name. Records that sound alike go in the same block.

Prefix blocking: Take the first 3 characters of the normalized name. "acm" → block with all values starting with "acm".

Zip code prefix blocking: Group by first 3 digits of zip code. This handles the common case where addresses are slightly wrong but geographically close.

Multi-pass blocking: Run multiple blocking strategies and take the union of candidate pairs. This recovers recall lost by any single strategy.

Here's how to implement a simple prefix-blocking strategy in M:

let
    // Add a blocking key to each table
    AddBlockKey = (tbl as table, nameCol as text) as table =>
        Table.AddColumn(
            tbl,
            "BlockKey",
            each Text.Start(
                Text.Lower(
                    Text.Select(Record.Field(_, nameCol), {"a".."z", "0".."9"})
                ),
                3
            ),
            type text
        ),

    // Apply to both tables
    MasterWithBlock = AddBlockKey(CustomerMaster, "CompanyName"),
    LeadsWithBlock  = AddBlockKey(CRMLeads, "Company"),

    // Exact join on BlockKey — this is our candidate pair generator
    CandidatePairs = Table.Join(
        MasterWithBlock,
        "BlockKey",
        LeadsWithBlock,
        "BlockKey",
        JoinKind.Inner
    ),

    // Now score only candidate pairs — much smaller set
    Scored = Table.AddColumn(
        CandidatePairs,
        "Scores",
        each ScoreRecordPair(
            [CompanyName = [CompanyName], City = [City], Phone = [Phone], ZipCode = [ZipCode]],
            [CompanyName = [Company],     City = [City.1], Phone = [Phone.1], ZipCode = [ZipCode.1]]
        )
    ),

    // Expand scores and filter by threshold
    Expanded = Table.ExpandRecordColumn(
        Scored,
        "Scores",
        {"NameScore", "CityScore", "PhoneScore", "ZipScore", "CompositeScore"}
    ),

    Filtered = Table.SelectRows(Expanded, each [CompositeScore] >= 0.70)
in
    Filtered

Architecture tip: In Power Query connected to a relational source (SQL Server, Snowflake, etc.), the blocking join can potentially fold to the server. The scoring logic almost certainly won't fold — it'll run in the M engine on your local machine or gateway. Structure your query so the blocking step does as much filtering as possible before you pull data across the wire.

Multi-Pass Blocking to Recover Recall

Sometimes your blocking key itself has errors. "Acme" vs "Akme" won't share a 3-character prefix. Multi-pass blocking addresses this:

let
    // Block 1: First 3 chars of name
    Block1 = Table.Join(
        Table.AddColumn(MasterWithBlock, "BlockKey", each Text.Start(Text.Lower([CompanyName_Normalized]), 3)),
        "BlockKey",
        Table.AddColumn(LeadsWithBlock,  "BlockKey", each Text.Start(Text.Lower([Company_Normalized]), 3)),
        "BlockKey",
        JoinKind.Inner
    ),

    // Block 2: 5-digit zip code (catches name errors in same location)
    Block2 = Table.Join(
        Table.SelectRows(CustomerMaster, each [ZipCode] <> null and [ZipCode] <> ""),
        "ZipCode",
        Table.SelectRows(CRMLeads, each [ZipCode] <> null and [ZipCode] <> ""),
        "ZipCode",
        JoinKind.Inner
    ),

    // Union both candidate sets and deduplicate
    AllCandidates = Table.Distinct(
        Table.Combine({Block1, Block2}),
        {"CustomerID", "LeadID"}  // dedup by the pair
    )
in
    AllCandidates

You now have a candidate set that captures both the "same name prefix" bucket and the "same location" bucket. You'll score every pair in this combined set and let the composite score do the final filtering.

---

Handling the Deduplication Problem at Scale with `Table.Buffer`

One of the most common production failures with fuzzy matching in Power Query is re-evaluation. When you reference a table multiple times — as you do when generating candidate pairs in a cross-join-like structure — Power Query may re-evaluate the upstream query for each reference. This turns a 5-minute query into a 2-hour one.

Table.Buffer forces eager evaluation of a table at that point in the query plan. Use it strategically on intermediate tables that are expensive to compute and referenced multiple times:

let
    // Buffer the normalized tables before the blocking join
    // This prevents them from being re-evaluated for every candidate pair
    MasterBuffered = Table.Buffer(
        Table.AddColumn(CustomerMaster, "Name_Normalized",
            each NormalizeString([CompanyName]))
    ),

    LeadsBuffered = Table.Buffer(
        Table.AddColumn(CRMLeads, "Company_Normalized",
            each NormalizeString([Company]))
    ),

    // Now the blocking join uses cached versions
    CandidatePairs = Table.Join(
        MasterBuffered, "BlockKey",
        LeadsBuffered,  "BlockKey",
        JoinKind.Inner
    )
in
    CandidatePairs

Warning: Table.Buffer loads the entire table into memory at evaluation time. On very large datasets (millions of rows), this can exhaust Power Query's memory budget. For those cases, you're better off pushing the blocking join to the data source using native query or a dataflow, and only bringing candidate pairs into Power Query for scoring.

---

Validating Your Match Quality

Building a fuzzy matching pipeline without validation is an act of faith, not engineering. You need to understand two metrics:

• Precision: Of the pairs you called matches, what fraction are true matches? (How much junk did you include?)
• Recall: Of all the true matches, what fraction did your pipeline find? (How much did you miss?)

Creating a Ground Truth Sample

Manually label 100–200 record pairs as true matches or non-matches. In Power Query, store this as a reference table:

let
    GroundTruth = Table.FromRecords({
        [CustomerID = "C001", LeadID = "L001", TrueMatch = true],
        [CustomerID = "C001", LeadID = "L005", TrueMatch = true],
        [CustomerID = "C002", LeadID = "L002", TrueMatch = true],
        [CustomerID = "C003", LeadID = "L003", TrueMatch = true],
        [CustomerID = "C001", LeadID = "L004", TrueMatch = false],  // false positive to catch
        [CustomerID = "C004", LeadID = "L004", TrueMatch = true]
    })
in
    GroundTruth

Computing Precision and Recall in M

let
    // Your pipeline's predictions
    Predictions = ScoredAndFiltered,  // From your fuzzy join pipeline

    // Add a "Predicted = true" column to all predictions
    PredictedPositives = Table.AddColumn(Predictions, "Predicted", each true),

    // Join predictions to ground truth
    Joined = Table.Join(
        GroundTruth, {"CustomerID", "LeadID"},
        PredictedPositives, {"CustomerID", "LeadID"},
        JoinKind.FullOuter
    ),

    // Fill nulls — rows only in GroundTruth weren't predicted; rows only in Predictions aren't in GT
    WithDefaults = Table.ReplaceValue(
        Table.ReplaceValue(Joined, null, false, Replacer.ReplaceValue, {"TrueMatch"}),
        null, false, Replacer.ReplaceValue, {"Predicted"}
    ),

    // Count confusion matrix cells
    TP = Table.RowCount(Table.SelectRows(WithDefaults, each [TrueMatch] = true  and [Predicted] = true)),
    FP = Table.RowCount(Table.SelectRows(WithDefaults, each [TrueMatch] = false and [Predicted] = true)),
    FN = Table.RowCount(Table.SelectRows(WithDefaults, each [TrueMatch] = true  and [Predicted] = false)),

    Precision = if (TP + FP) = 0 then null else TP / (TP + FP),
    Recall    = if (TP + FN) = 0 then null else TP / (TP + FN),
    F1        = if (Precision = null or Recall = null or (Precision + Recall) = 0)
                then null
                else 2 * Precision * Recall / (Precision + Recall),

    Results = #table(
        type table [Metric = text, Value = number],
        {
            {"True Positives",  TP},
            {"False Positives", FP},
            {"False Negatives", FN},
            {"Precision",       Number.Round(Precision, 4)},
            {"Recall",          Number.Round(Recall,    4)},
            {"F1 Score",        Number.Round(F1,        4)}
        }
    )
in
    Results

Run this validation query every time you tune your threshold or weights. Look for the precision/recall tradeoff: lowering your threshold improves recall but hurts precision. Your target depends on business context. For deduplication of a customer master where a false positive merge destroys data, bias toward precision. For marketing lead matching where missing a connection costs revenue, bias toward recall.

---

Hands-On Exercise

Work through this complete scenario. A healthcare network is consolidating two physician directories — one from their main EHR system and one from an acquired hospital's system. Physicians appear in both, but name formatting, credential suffixes, and NPI formatting differ between the systems.

Dataset A (Main EHR):

[PhysicianID = "P001", Name = "Dr. Sarah J. Mitchell, MD", NPI = "1234567890", Specialty = "Cardiology", State = "TX"]
[PhysicianID = "P002", Name = "James Okafor, M.D.", NPI = "2345678901", Specialty = "Neurology", State = "TX"]
[PhysicianID = "P003", Name = "Christina Lee-Wong, DO", NPI = "3456789012", Specialty = "Pediatrics", State = "TX"]
[PhysicianID = "P004", Name = "Robert A. Hernandez", NPI = "4567890123", Specialty = "General Surgery", State = "TX"]

Dataset B (Acquired Hospital):

[DocID = "D101", FullName = "Sarah Mitchell MD", NPI = "1234567890", Dept = "Cardiology"]
[DocID = "D102", FullName = "James C. Okafor", NPI = "", Dept = "Neurology"]
[DocID = "D103", FullName = "Christina Wong DO", NPI = "3456789012", Dept = "Pediatrics"]
[DocID = "D104", FullName = "Roberto Hernandez MD", NPI = "", Dept = "Surgery"]
[DocID = "D105", FullName = "Angela Torres, RN", NPI = "", Dept = "Cardiology"]

Your tasks:

1. Write a normalization function specifically for physician names that strips credential suffixes (MD, DO, RN, M.D., etc.) and honorifics (Dr., Dr), removes punctuation, and lowercases.

2. Build a blocking strategy using NPI (exact when available) as the first block, and name prefix as the second block. Combine them.

3. Implement a two-field composite scorer: NPI exact match (weight 0.6) and normalized name Jaccard similarity (weight 0.4). Handle null NPIs gracefully by redistributing their weight to the name score.

4. Run your pipeline and identify which physician from dataset B maps to which from dataset A, which are new records (should get a new consolidated ID), and flag D105 as a non-physician that should be excluded from the physician master.

5. Tune your threshold so that "Roberto Hernandez" matches "Robert A. Hernandez" (they're the same person) but a hypothetical "Robert Anderson" would not.

The expected output is a unified physician master with one row per unique physician, a new consolidated PhysicianMasterID, and a source-tracking column indicating which systems the physician appeared in.

---

Common Mistakes and Troubleshooting

Mistake 1: Using Default Threshold 0.8 Without Testing It

The default threshold in Power Query's UI is 0.8, and it's almost always too high for real-world data. "Blue Ridge Mfg" vs "Blue Ridge Manufacturing" scores around 0.62. "Bob Smith" vs "Robert Smith" scores around 0.45. Run your transformation table first, then test your threshold against a known sample. Start at 0.6 and work up.

Mistake 2: Fuzzy Matching on Non-Normalized Fields

Applying Table.FuzzyJoin to a raw company name column that still has mixed case, trailing spaces, inconsistent punctuation, and no abbreviation expansion means your n-grams are contaminated with noise characters. Every period, comma, and inconsistent capitalization reduces the effective overlap. Always normalize first, even if you're going to use the built-in functions.

Mistake 3: Not Using `Table.Buffer` and Wondering Why It's Slow

If your fuzzy join takes 45 minutes on 50,000 rows, the first thing to check is whether your source tables are being re-evaluated repeatedly. Add Table.Buffer to both sides of your join before the fuzzy operation and re-run. A 10x speedup is not unusual.

Mistake 4: Using `NumberOfMatches = 1` When You Should Review Multiple

In deduplication scenarios, setting NumberOfMatches = 1 causes the engine to return only the best match per left-side row. But if two records are nearly equally similar (say 0.81 and 0.79), you probably want to see both and make a human decision rather than blindly taking the top result. During tuning, set NumberOfMatches = 3 to understand the match landscape, then switch back to 1 for production.

Mistake 5: Not Handling Null Values Before Fuzzy Operations

Table.FuzzyJoin handles nulls reasonably — it treats null as a zero-length string and it rarely matches against anything above threshold. But in your custom M scoring functions, nulls will propagate in unexpected ways. The Record.Field(_, "Phone") ?? "" null-coalescing pattern is your friend. Test your scoring function explicitly with null inputs on every field.

Mistake 6: Treating Fuzzy Match Output as Ground Truth

The output of any fuzzy matching pipeline is candidate matches, not verified matches. For low-stakes scenarios (marketing segmentation), automated matching at a reasonable threshold is fine. For anything involving financial records, medical records, or legal identity, you need a human review step for all pairs above your "auto-accept" threshold and below your "auto-reject" threshold. Build a review queue as part of your data model.

Troubleshooting: The Query Returns No Rows

If your fuzzy join returns nothing, the most common causes are:

1. Threshold too high — try 0.4 to see if anything comes through at all
2. Both sides have the same normalization problem (e.g., both have the string "N/A" as a company name, and they match each other perfectly at 1.0 — but if you filtered for threshold > 1.0, they'd disappear; check your filter logic)
3. Column types are wrong — fuzzy functions expect text type; if your join column is a different type, the function silently fails

Troubleshooting: Too Many False Positives

If you're getting matches that clearly shouldn't match:

1. Your threshold is too low — raise it incrementally
2. Your strings are too short — trigram Jaccard breaks down on strings under 6 characters; consider using a different comparison for short fields
3. Common tokens are dominating the score — two companies both named "International Holdings XYZ" and "International Holdings ABC" share many trigrams from "international" and "holdings"; consider extracting and down-weighting common tokens via your transformation table

---

Summary and Next Steps

You've gone from the theoretical foundation of how Power Query's n-gram Jaccard engine works all the way to a production-ready probabilistic record linkage pipeline with blocking, multi-field scoring, and precision/recall validation. The key takeaways are:

The engine matters. Knowing that Power Query uses Jaccard similarity over character n-grams tells you exactly why short strings behave poorly, why abbreviations tank your scores, and why a transformation table is so powerful.

Preprocessing is the highest-leverage work. More ROI comes from normalizing your strings and building a transformation table than from any amount of threshold tuning.

Blocking is non-negotiable at scale. Cross-joining two tables of any meaningful size without blocking is an O(n²) operation. Structure your pipeline as: normalize → block → score → threshold.

Validate against a labeled sample. You cannot know whether your pipeline is working without precision and recall numbers. Build the validation query and run it every time you change a parameter.

Match output is candidates, not truth. Build your data model with this assumption. Store match confidence scores, implement a review queue for borderline cases, and make it easy to correct mistakes.

Where to go from here:

• Integrate with Azure Purview or Dataverse for enterprise-scale entity resolution with persistent match decisions and audit trails
• Explore the Comparer module in M — Comparer.OrdinalIgnoreCase and related functions give you control over how equality checks behave in non-fuzzy joins, which matters when building hybrid exact/fuzzy pipelines
• Investigate Power Query dataflows in Fabric or Power BI Premium, which offer scheduled refresh and incremental processing — essential when your source data is updated daily and you don't want to re-run fuzzy matching on the entire dataset
• Extend to address standardization using USPS address normalization tables as your transformation table source, which can dramatically improve geographic record linkage
• Consider Python or R scripts embedded in Power Query for more sophisticated similarity algorithms — Jaro-Winkler for name matching, Damerau-Levenshtein for short codes — when the built-in Jaccard doesn't fit your data profile

The data quality problem you started with — 847,000 rows from three systems, all slightly wrong — is now a solvable engineering problem, not a manual nightmare.