Analistable

How to use fuzzy merge in Power Query

By the Analistable team · Updated · 5 min read

In the Merge dialog, tick Use fuzzy matching to perform the merge and open Fuzzy matching options. Set the Similarity threshold (0 to 1, default 0.80), tick Ignore case, and optionally Match by combining text parts. Lower thresholds match more loosely. Always add the similarity score and review borderline matches before trusting the result.

Part of our guide: How to merge data in Power BI, Power Query and Tableau

When fuzzy matching helps

Names that don't match exactly
InvoicesCRMExact mergeFuzzy merge (0.8)
Hart & CoHart and CoNo matchLikely match
Nordic Supply ApSNordic SupplyNo matchLikely match
bakery luneBakery LuneNo matchMatch (Ignore case)
Acme LtdAcme LabsNo matchPossible false match

The last row is the risk: fuzzy matching finds similar text, not the same company.

Step by step

  1. Load both tables into Power Query and choose Merge Queries.
  2. Select the text columns to match. Fuzzy matching works on text columns only.
  3. Tick Use fuzzy matching to perform the merge.
  4. Open Fuzzy matching options: set the similarity threshold, tick Ignore case, and set Maximum number of matches to 1 if each row should match at most one record.
  5. Click OK and expand the columns you need.

The options

Fuzzy matching options
OptionEffect
Similarity threshold0–1; 1 means exact. 0.8 is the default; try 0.85–0.9 for company names
Ignore caseTreat upper and lower case as equal
Match by combining text partsMatches “Micro soft” with “Microsoft”
Maximum number of matchesLimits matches per row; 1 avoids duplicated rows
Transformation tableA two-column table (From, To) of known equivalents, e.g. “&” → “and”

Check the matches

Expand the matched name next to the original and add a similarity column: in the M code for the merge, add the SimilarityColumnName option:

= Table.FuzzyNestedJoin(Invoices, {"Customer"}, CRM, {"Name"}, "CRM", JoinKind.LeftOuter,
    [IgnoreCase = true, Threshold = 0.85, NumberOfMatches = 1, SimilarityColumnName = "Score"])

Expand the Score column too, sort ascending and review the lowest-scoring matches by hand. Clean obvious noise first (legal suffixes like Ltd, GmbH, ApS; punctuation) — that improves accuracy more than lowering the threshold.

Fuzzy merge is in Excel for Microsoft 365 and Power BI. For exact-key joins, see Merge Queries join kinds.

A transformation table for known variants

When you know certain variants mean the same thing, give fuzzy merge a transformation table with From and To columns:

Transformation table
FromTo
Smith & SonsSmith and Sons Ltd
ACME IntlAcme International
J. BrownJonathan Brown

Select it under Fuzzy matching options → Transformation table. Each row maps a whole value to the value it should be treated as, so list complete names rather than fragments such as "Ltd"; mapped values then match their target even when the similarity score alone would miss them.

Worked example: invoices to a CRM list

Twelve invoice customer names are matched against a CRM account list. With an exact merge, 7 of 12 rows match. After cleaning (trim, remove punctuation, strip legal suffixes) and a fuzzy merge at 0.85 with Ignore case, the results fall into three groups:

Reviewing fuzzy matches
GroupRowsAction
Exact or score 1.07Accept
Score 0.85–0.994Review by eye; accept 3, reject 1
No match1Add to CRM or map manually

7 + 3 accepted matches gives 10 of 12 invoices linked to an account. The rejected match and the unmatched row go into a small mapping table that you merge exactly next month, so the review work isn't repeated. The numbers here are illustrative: your own match rates depend entirely on how messy the names are.

Clean before you fuzzy match

Fuzzy scores are pulled down by noise that has nothing to do with identity. Add these steps to both queries before the merge:

= Table.TransformColumns(Source, {{"Customer", each
    Text.Trim(
      Text.Replace(Text.Replace(Text.Replace(Text.Lower(_), ".", ""), ",", ""), " ltd", "")
    ), type text}})

Lower-casing, removing punctuation and stripping common suffixes often turns a fuzzy problem into an exact one. Match on the cleaned column, but expand and keep the original name so reports still show it as written. Be careful with suffix removal on short names: “Acme Ltd” and “Acme Labs” become closer, not further apart.

Troubleshooting fuzzy merges

Symptom, cause and fix
SymptomCauseFix
Fuzzy option is missingOlder Excel version, or a non-text key columnUse Microsoft 365 or Power BI; set the key type to Text
Wrong companies matchedThreshold too low, or names dominated by common wordsRaise the threshold; strip words like “Group” or “Services” before matching
Obvious matches missedAbbreviations or reordered wordsTick Match by combining text parts; add a transformation table
Refresh is slowFuzzy matching compares many pairs of namesFilter both tables first; merge exactly where possible and fuzzy only the leftovers

Exact first, fuzzy for the rest

A reliable monthly routine is two passes. First merge exactly on the cleaned name or an ID and keep the matches. Then take the Left anti result (rows that didn't match) and fuzzy merge only those. Fewer rows go through fuzzy matching, refresh is quicker, and you review a short list instead of the whole table. Append the two sets back together with a column that records whether each row was matched exactly or fuzzily.

For contact lists, matching on email first and names second usually beats fuzzy matching alone; see merging contact lists without duplicates.

When not to use fuzzy merge

Fuzzy matching is a starting point, not an answer, for anything with money attached. If both systems share an ID, email address or VAT number, match on that exactly. If names are the only link, use the fuzzy result to build a reviewed mapping table and merge exactly against that from then on, so the same pair is never re-scored differently after a threshold change.

Frequently asked questions

What similarity threshold should I use?
Start at the default 0.8, review the matches, then raise it (0.85–0.9) if you see false matches or lower it if obvious matches are missed.
Can fuzzy merge match numbers or dates?
No. Fuzzy matching only works on text columns.
Why does fuzzy merge return duplicate rows?
A row matched more than one record. Set Maximum number of matches to 1.
Should I clean names before a fuzzy merge?
Yes. Lower-case, trim, remove punctuation and strip legal suffixes in both tables first. It raises scores for true matches and often turns fuzzy matches into exact ones.
How do I see how similar each fuzzy match was?
Add SimilarityColumnName to the Table.FuzzyNestedJoin options and expand that column; sort it ascending to review the weakest matches.

Related guides