Power Query's native fuzzy join hits hard limits the moment you need weighted multi-field scoring, custom blocking strategies, or explainable match decisions. This deep-dive lesson shows you how to implement Levenshtein, Jaro-Winkler, and Soundex from scratch in M and wire them into a production-grade entity resolution pipeline.
You've got a customer database with 80,000 records. Marketing loaded it from a CRM export, finance added rows from an ERP system, and someone pasted in a spreadsheet from a trade show. Now you need to deduplicate it. "Robert Smith" is also "Bob Smith," "R. Smith," and — thanks to a data entry error — "Rbort Smit." The company "Acme Corp." appears as "ACME Corporation," "Acme Corp," and "Acme, Corp." The addresses don't match. The phone numbers are formatted differently. And your manager wants the clean file by 3pm.
Power Query's native fuzzy join, introduced in 2019, handles surprisingly many of these cases well enough — until it doesn't. The moment you need to weight certain fields more than others, combine multiple similarity signals, apply domain-specific normalization rules, implement blocking strategies for performance, or understand why two records were matched, you've hit the ceiling. The native implementation is a black box. It gives you a similarity threshold knob and a token distance option, and that's approximately it. For serious entity resolution work — the kind that actually shows up in data engineering, master data management, and compliance projects — you need to build your own machinery.
By the end of this lesson, you'll have built a complete record linkage system in Power Query M from scratch. You'll understand the underlying string similarity algorithms well enough to implement them yourself, know how to combine multiple similarity scores into a composite match score, apply blocking strategies that make the approach scale beyond toy datasets, and construct a deduplication workflow that produces auditable, explainable results.
What you'll learn:
This lesson assumes you're comfortable with Power Query M at an intermediate-to-advanced level. You should understand:
let...in expressions and function definitionsList.Generate, List.Accumulate, List.Transform)Table.AddColumn, Table.NestedJoin, Table.TransformColumns)each shorthand and custom function parametersIf you've never written a recursive function in M or don't know what List.Accumulate does, work through the intermediate M language lessons first. The algorithms here depend heavily on those primitives.
Before we build our own system, it's worth being precise about what the native fuzzy join does and where it breaks. When you use Table.FuzzyJoin or the "Use fuzzy matching" option in the merge dialog, Power Query applies a modified Jaccard similarity on token sets derived from the string. It tokenizes both strings (splitting on whitespace and some punctuation), then measures the overlap of those token sets. The threshold parameter sets the minimum similarity ratio for a match.
This works well for: matching "Microsoft Corporation" to "Microsoft Corp." It works poorly for: short strings where token overlap is meaningless, strings with transpositions and typos within tokens, numeric fields, cases where field order matters, or any situation where you want to weight "company name matches" more heavily than "city matches."
The native implementation also has no concept of blocking. It does a full O(n²) comparison, which is why Microsoft quietly caps the fuzzy join at about 50,000 rows before it starts timing out or crashing. And it gives you no insight into why records matched — just a join result.
Here's the core limitation in concrete terms: if you're matching "Acme Corp" to "ACME Corporation" on company name, and you also want to verify that the city field shows similarity above 0.8 and the phone number matches exactly, you cannot express that logic in native fuzzy join. You need a composable scoring system.
Before we write a single line of M, let's build a solid mental model of the three similarity functions we're going to implement. Understanding the math makes the code readable rather than magical.
Levenshtein distance counts the minimum number of single-character operations — insertions, deletions, and substitutions — required to transform one string into another. "kitten" to "sitting" has a Levenshtein distance of 3 (substitute 'k' for 's', substitute 'e' for 'i', insert 'g' at the end).
The algorithm uses dynamic programming. We build a matrix where M[i][j] represents the edit distance between the first i characters of string A and the first j characters of string B. The recurrence relation is:
M[i][j] = M[i-1][j-1] M[i][j] = 1 + min(M[i-1][j], M[i][j-1], M[i-1][j-1])This is inherently a nested loop computation, which doesn't map naturally onto M's functional paradigm. We need to use List.Accumulate to simulate the row-by-row construction of the DP matrix.
Jaro similarity measures how similar two strings are based on matching characters and transpositions. Two characters are "matching" if they're the same and within floor(max(len1,len2)/2) - 1 positions of each other. Jaro similarity is (m/len1 + m/len2 + (m-t/2)/m) / 3 where m is the count of matching characters and t is the number of transpositions.
Jaro-Winkler extends this by giving a bonus to strings that share a common prefix (up to 4 characters), reasoning that people rarely mistype the beginning of a word. This makes it particularly good for name matching — "Robert" and "Robret" score higher than "Robert" and "Trebor" even if the raw character counts are similar.
Soundex converts a word to a code based on how it sounds rather than how it's spelled. The algorithm: keep the first letter, then replace remaining consonants with digit codes (B/F/P/V → 1, C/G/J/K/Q/S/X/Z → 2, D/T → 3, L → 4, M/N → 5, R → 6), drop vowels and H/W/Y, remove consecutive duplicates, and pad or truncate to a 4-character code.
"Smith" and "Smythe" both produce "S530". "Robert" and "Rupert" both produce "R163". Soundex is not a similarity score — it's an exact-match key used for blocking and pre-filtering, which we'll use exactly that way.
Here's the full implementation. We'll walk through each piece.
let
LevenshteinDistance = (str1 as text, str2 as text) as number =>
let
s1 = Text.Lower(str1),
s2 = Text.Lower(str2),
len1 = Text.Length(s1),
len2 = Text.Length(s2),
// Early exits for trivial cases
result =
if s1 = s2 then 0
else if len1 = 0 then len2
else if len2 = 0 then len1
else
let
// Initialize first row: [0, 1, 2, ..., len2]
initialRow = List.Numbers(0, len2 + 1),
// For each character in s1, compute the next row
finalRow = List.Accumulate(
List.Numbers(0, len1), // iterate over index 0..len1-1
initialRow,
(currentRow, i) =>
let
c1 = Text.At(s1, i),
// Start new row with i+1 as the first element
newRowStart = {i + 1},
// Fill in the rest of the row
newRow = List.Accumulate(
List.Numbers(0, len2),
newRowStart,
(rowSoFar, j) =>
let
c2 = Text.At(s2, j),
cost = if c1 = c2 then 0 else 1,
above = currentRow{j + 1}, // deletion
left = List.Last(rowSoFar), // insertion
diagonal = currentRow{j}, // substitution
cellValue = List.Min({above + 1, left + 1, diagonal + cost})
in
rowSoFar & {cellValue}
)
in
newRow
)
in
List.Last(finalRow)
in
result
in
LevenshteinDistance
The key insight here is the double List.Accumulate. The outer one iterates over characters in str1, maintaining the "current row" of the DP matrix as its accumulator. The inner one iterates over characters in str2, building the next row cell by cell. At each cell, we look at three neighbors: the cell directly above (current row, one column right), the cell to the left (previous cell in the new row), and the diagonal (current row, same column).
Performance warning: This implementation is O(n×m) in computation and creates a lot of intermediate lists. For strings longer than ~50 characters, it will be noticeably slow when applied across thousands of record pairs. We'll address this with blocking later. For typical name/address matching (strings under 40 chars), it's acceptable.
To convert distance to a 0–1 similarity score, normalize it:
let
LevenshteinSimilarity = (str1 as text, str2 as text) as number =>
let
distance = LevenshteinDistance(str1, str2),
maxLen = Number.Max({Text.Length(str1), Text.Length(str2)}),
similarity = if maxLen = 0 then 1.0 else 1 - (distance / maxLen)
in
similarity
in
LevenshteinSimilarity
Jaro-Winkler is more complex to implement because finding "matching characters" requires tracking which positions have already been matched. Here's a clean implementation:
let
JaroWinkler = (str1 as text, str2 as text) as number =>
let
s1 = Text.Lower(str1),
s2 = Text.Lower(str2),
len1 = Text.Length(s1),
len2 = Text.Length(s2),
jaroScore =
if s1 = s2 then 1.0
else if len1 = 0 or len2 = 0 then 0.0
else
let
matchDistance = Number.IntegerDivide(Number.Max({len1, len2}), 2) - 1,
safeMatchDistance = Number.Max({matchDistance, 0}),
// For each char in s1, find first unmatched char in s2 within window
// We track matched indices in s2 as a list of booleans
initialState = {
List.Repeat({false}, len2), // s2Matched flags
{}, // matched chars from s1
{} // matched chars from s2
},
matchState = List.Accumulate(
List.Numbers(0, len1),
initialState,
(state, i) =>
let
s2Matched = state{0},
s1Matches = state{1},
s2Matches = state{2},
c1 = Text.At(s1, i),
windowStart = Number.Max({0, i - safeMatchDistance}),
windowEnd = Number.Min({len2 - 1, i + safeMatchDistance}),
windowIndices = List.Numbers(windowStart, windowEnd - windowStart + 1),
// Find first unmatched position in window where chars match
matchResult = List.Accumulate(
windowIndices,
{s2Matched, s1Matches, s2Matches, false},
(inner, j) =>
let
alreadyFound = inner{3},
currentS2Matched = inner{0}
in
if alreadyFound then inner
else if currentS2Matched{j} then inner
else if Text.At(s2, j) <> c1 then inner
else
let
updatedFlags = List.ReplaceRange(
currentS2Matched, j, 1, {true}
)
in
{
updatedFlags,
inner{1} & {c1},
inner{2} & {Text.At(s2, j)},
true
}
),
newS2Matched = matchResult{0},
newS1Matches = matchResult{1},
newS2Matches = matchResult{2}
in
{newS2Matched, newS1Matches, newS2Matches}
),
matchedS1 = matchState{1},
matchedS2 = matchState{2},
m = List.Count(matchedS1),
jaroResult =
if m = 0 then 0.0
else
let
// Count transpositions
transpositions = List.Accumulate(
List.Numbers(0, m),
0,
(t, k) => if matchedS1{k} <> matchedS2{k} then t + 1 else t
),
t = Number.IntegerDivide(transpositions, 2)
in
(m / len1 + m / len2 + (m - t) / m) / 3
in
jaroResult,
// Winkler prefix bonus (up to 4 matching prefix chars)
prefixLength = List.Accumulate(
{0, 1, 2, 3},
0,
(p, i) =>
if i < Text.Length(s1) and i < Text.Length(s2)
and Text.At(s1, i) = Text.At(s2, i)
then p + 1
else p
),
// Winkler scaling factor is typically 0.1
winklerScore = jaroScore + (prefixLength * 0.1 * (1 - jaroScore))
in
Number.Min({winklerScore, 1.0})
in
JaroWinkler
The Winkler prefix bonus adds up to 0.1 × 4 × (1 - jaroScore) = 0.4 × (1 - jaroScore). This is a significant boost for names with matching prefixes — exactly what we want for "Robert" vs. "Robret" or "McDonald" vs. "MacDonald."
Tip: Jaro-Winkler is generally superior to Levenshtein for personal names. Use Levenshtein for addresses, product codes, and other fields where transpositions are equally likely anywhere in the string.
Soundex gives us a fast phonetic key we can use for blocking — grouping records that might sound similar so we only run the expensive string comparisons within those groups.
let
Soundex = (input as text) as text =>
let
cleaned = Text.Upper(Text.Select(input, {"A".."Z"})),
result =
if Text.Length(cleaned) = 0 then "0000"
else
let
firstChar = Text.Start(cleaned, 1),
rest = Text.End(cleaned, Text.Length(cleaned) - 1),
// Soundex digit mapping
SoundexDigit = (c as text) as text =>
if List.Contains({"B","F","P","V"}, c) then "1"
else if List.Contains({"C","G","J","K","Q","S","X","Z"}, c) then "2"
else if List.Contains({"D","T"}, c) then "3"
else if c = "L" then "4"
else if List.Contains({"M","N"}, c) then "5"
else if c = "R" then "6"
else "0", // vowels, H, W, Y → 0 (ignored)
// Build coded string, collapsing adjacent identical codes
initialState = {
"", // accumulated code digits
SoundexDigit(firstChar) // last code (to detect duplicates)
},
coded = List.Accumulate(
Text.ToList(rest),
initialState,
(state, c) =>
let
digits = state{0},
lastCode = state{1},
thisCode = SoundexDigit(c)
in
if thisCode = "0" or thisCode = lastCode
then {digits, thisCode}
else if Text.Length(digits) >= 3
then {digits, thisCode}
else {digits & thisCode, thisCode}
),
rawCode = firstChar & coded{0},
// Pad or truncate to exactly 4 characters
paddedCode = Text.PadEnd(rawCode, 4, "0"),
finalCode = Text.Start(paddedCode, 4)
in
finalCode
in
result
in
Soundex
Important nuance: The standard Soundex algorithm is famously imperfect. "Lee" and "Lie" produce the same code. "Smith" and "Schmidt" don't. For production work on names that include non-English characters or names from non-Anglo-Saxon traditions, consider implementing Double Metaphone instead — it's more complex but handles a much wider range of phonetic patterns. For this lesson, Soundex illustrates the concept; the implementation pattern transfers directly to more sophisticated phonetic algorithms.
Now we assemble the matching engine. The philosophy here is: calculate a normalized similarity score (0 to 1) for each field, then compute a weighted average to get a composite match score. Records above a threshold are match candidates.
First, define the weights and thresholds for a customer record scenario:
let
MatchConfig = [
CompanyName = [Weight = 0.40, Threshold = 0.70, Algorithm = "JaroWinkler"],
ContactName = [Weight = 0.25, Threshold = 0.75, Algorithm = "JaroWinkler"],
City = [Weight = 0.15, Threshold = 0.80, Algorithm = "Levenshtein"],
Phone = [Weight = 0.20, Threshold = 0.90, Algorithm = "Exact"]
],
GlobalMatchThreshold = 0.72
in
MatchConfig
Now build the composite scorer as a function:
let
CompositeScore = (
record1 as record,
record2 as record,
config as record
) as record =>
let
// Helper to safely get text value
SafeText = (r as record, field as text) as text =>
let val = Record.FieldOrDefault(r, field, null)
in if val = null then "" else Text.From(val),
// Score a single field pair
ScoreField = (f1 as text, f2 as text, algorithm as text) as number =>
if f1 = "" or f2 = "" then 0.0
else if algorithm = "Exact" then (if f1 = f2 then 1.0 else 0.0)
else if algorithm = "JaroWinkler" then JaroWinkler(f1, f2)
else if algorithm = "Levenshtein" then LevenshteinSimilarity(f1, f2)
else 0.0,
// Compute scores for each configured field
fieldNames = Record.FieldNames(config),
fieldScores = List.Transform(
fieldNames,
(fieldName) =>
let
fieldConfig = Record.Field(config, fieldName),
weight = fieldConfig[Weight],
algorithm = fieldConfig[Algorithm],
v1 = SafeText(record1, fieldName),
v2 = SafeText(record2, fieldName),
rawScore = ScoreField(v1, v2, algorithm),
// Normalize: scores below per-field threshold count as 0
// This prevents "everything kinda matches" from inflating the score
effectiveScore = if rawScore >= fieldConfig[Threshold]
then rawScore
else rawScore * 0.5
in
[
Field = fieldName,
Score = rawScore,
EffectiveScore = effectiveScore,
Weight = weight,
WeightedScore = effectiveScore * weight
]
),
totalWeight = List.Sum(List.Transform(fieldScores, each _[Weight])),
compositeScore = List.Sum(List.Transform(fieldScores, each _[WeightedScore])) / totalWeight,
result = [
CompositeScore = compositeScore,
FieldScores = fieldScores,
IsMatch = compositeScore >= 0.72
]
in
result
in
CompositeScore
The "effective score" logic deserves explanation. If your company name similarity is 0.50 — not great, but not zero — you don't want that dragging your composite score down to where a genuinely good phone number match gets masked. But you also don't want a half-match inflating the score as if it were a full match. Halving below-threshold scores is a simple but effective penalty. More sophisticated implementations use sigmoid functions here, but the halving approach is transparent and auditable.
Without blocking, a 10,000-record dataset requires 50 million pairwise comparisons. That's not happening in Power Query. Blocking reduces this to a manageable number by only comparing records that share a "blocking key" — some cheap-to-compute signal that potential matches are likely to share.
Our blocking strategy will use Soundex of the first word of the company name, combined with the first two characters of the city name. Records must share this block key to be compared. This is a pragmatic choice: it will miss some genuine matches (false negatives) where the Soundex encoding diverges, but it reduces comparisons dramatically.
let
BuildBlockingKey = (companyName as text, city as text) as text =>
let
// Take first word of company name, strip common suffixes first
NormalizeCompany = (name as text) as text =>
let
lowered = Text.Lower(name),
stripped = List.Accumulate(
{"corporation", "corp.", "corp", "incorporated", "inc.",
"inc", "llc", "ltd.", "ltd", "limited", "co.", "co"},
lowered,
(s, suffix) =>
if Text.EndsWith(s, " " & suffix)
then Text.Start(s, Text.Length(s) - Text.Length(suffix) - 1)
else s
),
firstWord = Text.BeforeDelimiter(Text.Trim(stripped), " "),
result = if Text.Length(firstWord) = 0 then stripped else firstWord
in
result,
normalizedCompany = NormalizeCompany(companyName),
soundexKey = Soundex(normalizedCompany),
cityPrefix = Text.Start(Text.Upper(Text.Select(city, {"A".."Z"})), 2),
blockKey = soundexKey & "_" & cityPrefix
in
blockKey
in
BuildBlockingKey
Notice the company name normalization step. Stripping legal suffixes before computing Soundex is critical — "Acme Corp" and "Acme Corporation" need to land in the same block, and they will once "corp" and "corporation" are removed. This kind of domain-specific preprocessing is where custom solutions always outperform generic ones.
Now apply the blocking key to your source table and join it to itself:
let
Source = /* your customer table */,
// Add blocking key and an index for self-join deduplication
WithBlockingKey = Table.AddIndexColumn(
Table.AddColumn(
Source,
"BlockKey",
each BuildBlockingKey([CompanyName], [City])
),
"RecordIndex", 1, 1, Int64.Type
),
// Self-join on block key
SelfJoined = Table.NestedJoin(
WithBlockingKey,
{"BlockKey"},
WithBlockingKey,
{"BlockKey"},
"Candidates",
JoinKind.Inner
),
// Expand candidates
Expanded = Table.ExpandTableColumn(
SelfJoined,
"Candidates",
{"RecordIndex", "CompanyName", "ContactName", "City", "Phone"},
{"Cand_Index", "Cand_CompanyName", "Cand_ContactName", "Cand_City", "Cand_Phone"}
),
// Remove self-matches and duplicate pairs (A-B vs B-A)
FilteredPairs = Table.SelectRows(
Expanded,
each [RecordIndex] < [Cand_Index]
)
in
FilteredPairs
The RecordIndex < Cand_Index filter is important. Without it, you'd get both the "A vs B" and "B vs A" comparison, and every record self-matched. By requiring the left index to be less than the right, each pair appears exactly once.
Now we wire everything together into the actual deduplication workflow. This is the moment where all the pieces become a coherent system.
let
// --- Load and normalize source data ---
RawData = /* your source table */,
NormalizeText = (t as nullable text) as text =>
if t = null then ""
else Text.Trim(Text.Lower(
Text.Select(t,
{"a".."z", "A".."Z", "0".."9", " ", "-", ".", ",", "&"}
)
)),
Normalized = Table.TransformColumns(
Table.AddIndexColumn(RawData, "RecordID", 1, 1, Int64.Type),
{
{"CompanyName", NormalizeText, type text},
{"ContactName", NormalizeText, type text},
{"City", NormalizeText, type text},
{"Phone",
each Text.Select(Text.From(_), {"0".."9"}), // digits only
type text}
}
),
// --- Add blocking keys ---
WithBlocking = Table.AddColumn(
Normalized,
"BlockKey",
each BuildBlockingKey([CompanyName], [City])
),
// --- Generate candidate pairs via blocking ---
SelfJoined = Table.NestedJoin(
WithBlocking, {"BlockKey"},
WithBlocking, {"BlockKey"},
"Candidates", JoinKind.Inner
),
Expanded = Table.ExpandTableColumn(
SelfJoined, "Candidates",
{"RecordID", "CompanyName", "ContactName", "City", "Phone"},
{"R_RecordID", "R_CompanyName", "R_ContactName", "R_City", "R_Phone"}
),
ValidPairs = Table.SelectRows(Expanded, each [RecordID] < [R_RecordID]),
// --- Score each candidate pair ---
FieldConfig = [
CompanyName = [Weight = 0.40, Threshold = 0.70, Algorithm = "JaroWinkler"],
ContactName = [Weight = 0.25, Threshold = 0.75, Algorithm = "JaroWinkler"],
City = [Weight = 0.15, Threshold = 0.80, Algorithm = "Levenshtein"],
Phone = [Weight = 0.20, Threshold = 0.90, Algorithm = "Exact"]
],
Scored = Table.AddColumn(
ValidPairs,
"MatchResult",
each
let
r1 = [
CompanyName = [CompanyName],
ContactName = [ContactName],
City = [City],
Phone = [Phone]
],
r2 = [
CompanyName = [R_CompanyName],
ContactName = [R_ContactName],
City = [R_City],
Phone = [R_Phone]
]
in
CompositeScore(r1, r2, FieldConfig)
),
// --- Extract scores and filter to matches ---
WithScores = Table.AddColumn(
Scored, "CompositeScore",
each [MatchResult][CompositeScore], type number
),
Matches = Table.SelectRows(WithScores, each [CompositeScore] >= 0.72),
// --- Add human-readable explanation ---
WithExplanation = Table.AddColumn(
Matches,
"MatchExplanation",
each
let
fieldScores = [MatchResult][FieldScores],
lines = List.Transform(
fieldScores,
(fs) => fs[Field] & ": " &
Number.ToText(Number.Round(fs[Score], 2))
)
in
Text.Combine(lines, " | ")
),
// --- Final output ---
Output = Table.SelectColumns(
WithExplanation,
{"RecordID", "CompanyName", "R_RecordID", "R_CompanyName",
"CompositeScore", "MatchExplanation"}
)
in
Output
The MatchExplanation column is what makes this system defensible. When your manager asks "why did these two records get merged?", you can point to "CompanyName: 0.94 | ContactName: 0.88 | City: 1.0 | Phone: 0.0" and have a real conversation about whether this is a true match. Black-box deduplication creates more problems than it solves in regulated environments.
The pipeline above will produce wrong results on real data without additional preprocessing. Here's where practical experience matters more than algorithmic knowledge.
let
NormalizePhone = (phone as nullable text) as text =>
if phone = null then ""
else
let
digitsOnly = Text.Select(Text.From(phone), {"0".."9"}),
// Strip US country code if present
stripped = if Text.Length(digitsOnly) = 11
and Text.Start(digitsOnly, 1) = "1"
then Text.End(digitsOnly, 10)
else digitsOnly
in
stripped
in
NormalizePhone
"The Acme Company" and "Acme Company" need to match. Add a normalization step that strips leading articles:
let
StripLeadingStopWords = (name as text) as text =>
let
stopWords = {"the ", "a ", "an "},
result = List.Accumulate(
stopWords,
Text.Lower(Text.Trim(name)),
(s, sw) => if Text.StartsWith(s, sw)
then Text.End(s, Text.Length(s) - Text.Length(sw))
else s
)
in
Text.Trim(result)
in
StripLeadingStopWords
Your scoring function must treat null fields gracefully. Two records where both have null phone numbers shouldn't score 1.0 on the phone field — they should have that comparison omitted from the weighted average entirely. Modify CompositeScore to skip fields where both values are empty and re-weight accordingly.
Warning: The most common mistake in entity resolution is letting null-equality inflate match scores. "We don't have a phone number for either record, so the phone field scores 1.0" is a reasoning error that causes false merges.
Don't run Jaro-Winkler on every pair. Add a pre-filter:
// Before scoring, use quick exact-prefix check as a gate
QuickPreFilter = Table.SelectRows(
ValidPairs,
each Text.Start([CompanyName], 3) = Text.Start([R_CompanyName], 3)
or [Phone] = [R_Phone]
or Text.Start([CompanyName], 1) = Text.Start([R_CompanyName], 1)
)
This won't eliminate all non-matches (that's what the scoring does), but it dramatically reduces the number of expensive string comparisons you run.
Single-key blocking creates false negatives when the block key itself is wrong. Use multiple blocking passes with a union:
let
// Block 1: Soundex of company name + city prefix
Block1Pairs = /* as before */,
// Block 2: First 5 digits of phone
Block2Pairs = /* similar structure, using phone prefix as key */,
// Block 3: Soundex of contact last name + zip code
Block3Pairs = /* similar structure */,
AllCandidatePairs = Table.Distinct(
Table.Combine({Block1Pairs, Block2Pairs, Block3Pairs}),
{"RecordID", "R_RecordID"}
)
in
AllCandidatePairs
Using Table.Distinct with the pair's ID columns ensures you don't score the same pair multiple times when it appears in multiple blocks.
BufferedNormalized = Table.Buffer(Normalized),
BufferedWithBlocking = Table.Buffer(WithBlocking)
Table.Buffer forces evaluation and caches the result. In the self-join pattern, the same table is scanned twice (once for each side of the join). Buffering eliminates the double evaluation, which is crucial for large datasets.
Tip: Buffer before the self-join, not after. If you buffer after, you've already paid the double evaluation cost.
Build a complete deduplication system for the following scenario:
You have a supplier master table with these fields: SupplierID, SupplierName, ContactFirstName, ContactLastName, City, Country, TaxID.
Step 1: Define normalization rules appropriate for each field. Pay special attention to TaxID — what does a null TaxID mean for matching?
Step 2: Design a blocking strategy. What combination of fields makes a good blocking key for an international supplier table where City may be in different languages?
Step 3: Assign weights to each field. Write out your reasoning. Hint: TaxID when present should be nearly deterministic. Country alone should probably not count for much since most suppliers in your system will be from the same country.
Step 4: Implement the full pipeline and run it against this sample data (create it manually or download from the course data files):
| SupplierID | SupplierName | ContactFirstName | ContactLastName | City | Country | TaxID |
|---|---|---|---|---|---|---|
| 1 | Global Steel Inc | John | Murphy | Chicago | US | 123456789 |
| 2 | Global Steel Incorporated | J. | Murphy | Chicago | US | |
| 3 | Global Steel Inc. | John | Murphey | Chicago | US | 123456789 |
| 4 | Pacific Metals Corp | Wei | Zhang | Seattle | US | 987654321 |
| 5 | Pacific Metals Corporation | W. | Zhang | Seattle | US | 987654321 |
| 6 | Meridian Supplies | Ahmed | Al-Rashid | Dubai | AE | |
| 7 | Meridian Supply Co. | Ahmed | Al-Rashid | Dubai | AE |
Step 5: Review your match output. Did records 1, 2, and 3 all get linked? Did 4 and 5? Did 6 and 7? If you got false negatives, which blocking strategy was the cause?
Expected outcomes: With good blocking and scoring, you should get matches for all three clusters. The challenge is the abbreviated first name (J. vs John) and the typo in "Murphey" — your Jaro-Winkler implementation should handle both if weighted correctly.
"My Levenshtein function returns errors on empty strings."
Always add null and empty-string guards as the first thing in any string similarity function. Text.Length(null) throws in M. Check for null, then check for empty, before doing any character-level work.
"The self-join is timing out even with blocking." Check your blocking key cardinality. If most records have the same block key (common with data that has a dominant city or a narrow industry), you've effectively done no blocking. Aim for block sizes averaging under 200 records. Add more specificity to your block key.
"Records that should obviously match are scoring below threshold."
The most common cause is one field dragging the composite down. Look at the MatchExplanation output. If City similarity is 0.1 because one record says "NY" and the other says "New York," you need city normalization before scoring, not tuning the threshold.
"My Soundex function groups completely unrelated words together." This is expected and not a bug — it's the limitation of Soundex. "Lee" and "Lie" produce the same code. Soundex is for blocking, not matching. The scoring step will reject the false positives that blocking lets through. If you're seeing too many false positives in your final match output, the problem is in the scoring weights, not the blocking.
"The query takes 20 minutes to refresh."
Three things to check: (1) Is Table.Buffer applied before the self-join? (2) Are you running all three string algorithms on every pair, or applying pre-filters? (3) Is your block key selective enough? Use the query diagnostics tool (Tools → Start Diagnostics in Power Query Editor) to identify which step is consuming the most time.
"Scores look unreasonably high for clearly different records."
Check whether null-equality is inflating your scores. Two records where every field is null will score 0 only if your SafeText function returns "" for nulls and your ScoreField function returns 0 for two empty strings. Verify this in your implementation.
"I'm getting the A-B and B-A pair both in my output."
You forgot the RecordIndex < Cand_Index filter, or your index column has duplicate values. Make sure you add the index column before normalization so each row gets a unique, stable ID.
You've built a complete entity resolution engine from first principles in Power Query M. The key architectural decisions were:
Algorithm selection by field type. Jaro-Winkler for names, Levenshtein for addresses and codes, Soundex for phonetic blocking, exact match for identifiers.
Preprocessing before scoring. Normalization, suffix stripping, digit extraction, and stop word removal all happen before any similarity function touches the data. Garbage in, garbage out applies at every stage.
Blocking before comparing. The O(n²) problem is fundamental. Blocking doesn't just make this faster — it makes it possible at any meaningful scale.
Composite weighted scoring with per-field penalties. A single threshold on a single field can't capture the multi-dimensional nature of record identity. Weighted scores let you encode domain knowledge about which fields matter most.
Audit trails built in. The MatchExplanation column means every match decision is reviewable and defensible.
Where to go from here:
Implement Double Metaphone to replace Soundex for better phonetic blocking, especially for names from diverse linguistic backgrounds.
Add a transitive closure step. If A matches B and B matches C, all three should be in the same cluster even if A and C don't directly match. This requires a graph-based approach that you can implement using List.Generate for small datasets, or push to Python/R for large ones.
Move to incremental matching. The current pipeline re-scores all pairs on every refresh. For production, you want to only score new records against the existing master and against each other.
Explore probabilistic record linkage (Fellegi-Sunter model). The weights we assigned manually can be learned from data using EM algorithms — and that's a topic for an entire subsequent lesson.
Push heavy scoring to Dataverse or Azure. Power Query is an excellent place to orchestrate a matching pipeline, but for datasets over 100,000 records, the actual string comparison work belongs in a compute engine designed for it. Consider using Power Query to call Azure Functions or Python scripts that return pre-computed similarity scores.
The system you've built here is genuinely production-ready for datasets in the tens of thousands of records — and it's completely transparent, maintainable, and tunable by any data professional who understands M. That combination of capability and transparency is what separates engineered solutions from workarounds.