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
| 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 |
|---|---|
| Smith & Sons | Smith and Sons Ltd |
| ACME Intl | Acme International |
| J. Brown | Jonathan 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:
| Group | Rows | Action |
|---|---|---|
| Exact or score 1.0 | 7 | Accept |
| Score 0.85–0.99 | 4 | Review by eye; accept 3, reject 1 |
| No match | 1 | Add 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 | Fix |
|---|---|---|
| Fuzzy option is missing | Older Excel version, or a non-text key column | Use Microsoft 365 or Power BI; set the key type to Text |
| Wrong companies matched | Threshold too low, or names dominated by common words | Raise the threshold; strip words like “Group” or “Services” before matching |
| Obvious matches missed | Abbreviations or reordered words | Tick Match by combining text parts; add a transformation table |
| Refresh is slow | Fuzzy matching compares many pairs of names | Filter 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.