How to use fuzzy merge in Power Query
By the Analistable team · Updated · 2 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
| Invoices | CRM | Exact merge | Fuzzy merge (0.8) |
|---|---|---|---|
| Hart & Co | Hart and Co | No match | Likely match |
| Nordic Supply ApS | Nordic Supply | No match | Likely match |
| bakery lune | Bakery Lune | No match | Match (Ignore case) |
| Acme Ltd | Acme Labs | No match | Possible false match |
The last row is the risk: fuzzy matching finds similar text, not the same company.
Step by step
- Load both tables into Power Query and choose Merge Queries.
- Select the text columns to match. Fuzzy matching works on text columns only.
- Tick Use fuzzy matching to perform the merge.
- 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.
- Click OK and expand the columns you need.
The options
| Option | Effect |
|---|---|
| Similarity threshold | 0–1; 1 means exact. 0.8 is the default; try 0.85–0.9 for company names |
| Ignore case | Treat upper and lower case as equal |
| Match by combining text parts | Matches “Micro soft” with “Microsoft” |
| Maximum number of matches | Limits matches per row; 1 avoids duplicated rows |
| Transformation table | A 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:
| From | To |
|---|---|
| & | and |
| Ltd | Limited |
| Intl | International |
Select it under Fuzzy matching options → Transformation table. Power Query applies the mappings before scoring similarity, so these variants match with a higher score.
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.