Fuzzy matching in Power Query: standardise inconsistent names
How-to · By Tahir Nas · Examples run in our simulator · Updated
11 min read
Fuzzy matching in Power Query is a merge that pairs rows whose text is similar rather than identical, so "Olve Oil 500ml" can find "Olive Oil 500ml" in your product list. It works best as the middle step of three: normalise the names with ordinary text functions first, let the fuzzy merge suggest matches for whatever is left, then copy the matches you have checked into a mapping table that an exact merge uses on every refresh.
Start with a normalised key, not a fuzzy merge
Before reaching for fuzzy matching, look at how the names actually differ. In most exports the variations are mechanical: capital letters, extra spaces, a comma or full stop, "&" against "and", "500 ml" against "500ml". Rules can fix those, and a rule gives the same answer every time.
Build one function that turns a name into a comparison key, and add the key as a new column:
let
Sales = #table({"Order", "Product"}, {
{1, "Olive Oil 500ml"}, {2, "OLIVE OIL 500 ML"}, {3, "Olive oil, 500ml."}, {4, "Olive Oil (500ml)"},
{5, "Olve Oil 500ml"}, {6, "Sea Salt & Pepper 1kg"}, {7, "sea salt and pepper 1 kg"}
}),
Normalise = (t as nullable text) as nullable text =>
let
Upper = Text.Upper(t ?? ""),
Words = Text.Replace(Upper, "&", " AND "),
Units = Text.Replace(Text.Replace(Words, " ML", "ML"), " KG", "KG"),
Kept = Text.Select(Units, {"A".."Z", "0".."9", " "}),
OneSpace = Text.Combine(List.Select(Text.Split(Kept, " "), each _ <> ""), " ")
in
if t = null then null else OneSpace,
Keyed = Table.AddColumn(Sales, "Key", each Normalise([Product]), type nullable text)
in
KeyedResult
| Order | Product | Key |
|---|---|---|
| 1 | Olive Oil 500ml | OLIVE OIL 500ML |
| 2 | OLIVE OIL 500 ML | OLIVE OIL 500ML |
| 3 | Olive oil, 500ml. | OLIVE OIL 500ML |
| 4 | Olive Oil (500ml) | OLIVE OIL 500ML |
| 5 | Olve Oil 500ml | OLVE OIL 500ML |
| 6 | Sea Salt & Pepper 1kg | SEA SALT AND PEPPER 1KG |
| 7 | sea salt and pepper 1 kg | SEA SALT AND PEPPER 1KG |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Orders 1 to 4 all get the key OLIVE OIL 500ML, and orders 6 and 7 both get SEA SALT AND PEPPER 1KG. Seven spellings have become three keys. Only order 5, with the typo "Olve", still stands apart. That is the one case a rule can't fix.
The steps, in order:
- Upper case so that case never matters. Power Query compares text case-sensitively by default (see case sensitivity as a silent cause of missing matches), so this step alone fixes many missed matches.
- Replace "&" with " AND " so both spellings agree.
- Join units to their numbers. This rule is specific to the data, so write rules for the units and abbreviations that appear in yours.
- Keep only letters, digits and spaces with
Text.Select, which removes commas, full stops and brackets in one step (Text.Select, Microsoft Learn). The list{"A".."Z"}has no accented letters, so "CRÈME" would lose its È. Add the accented characters your data uses to the list. - Collapse repeated spaces by splitting on spaces, dropping empty pieces and joining again.
Apply the same function to both tables, the messy one and the reference list, then merge on the key with an ordinary exact merge. The exercise clean both keys before merging practises exactly this, and check key quality before you rely on it explains what to look for first.
How Power Query's fuzzy merge works
What the normalised key can't catch, such as typos, missing letters and transposed words, is where fuzzy merge helps. In the Merge dialog you tick Use fuzzy matching to perform the merge, then set the options.
Microsoft's fuzzy merge article (Microsoft Learn) states that fuzzy matching is only supported on merges over text columns, and that Power Query uses the Jaccard similarity algorithm to score pairs. The options are:
| Option | What it does | Default |
|---|---|---|
| Similarity threshold | Minimum score from 0.00 to 1.00 for a pair to match; 1.00 means exact | 0.80 |
| Ignore case | Compares text without regard to case | On |
| Match by combining text parts | Lets "Micro soft" match "Microsoft" | On in M (IgnoreSpace) |
| Show similarity scores | Adds the score to the expanded result | Off (no score column) |
| Number of matches | Maximum matching rows returned per input row | All matches |
| Transformation table | A From/To table of known equivalents | None |
The same options exist as fields of the joinOptions record of Table.FuzzyNestedJoin, whose reference page (Microsoft Learn) gives the defaults: IgnoreCase and IgnoreSpace are true, Threshold is 0.80, and with no NumberOfMatches every matching row is returned.
Here is the M equivalent of those dialog settings, with one match per row and a score column:
let
Sales = #table({"Order", "Product"}, {{1, "Olve Oil 500ml"}, {2, "Sea Salt & Pepper 1kg"}}),
Products = #table({"Product ID", "Product"}, {{"P-01", "Olive Oil 500ml"}, {"P-02", "Sea Salt and Pepper 1kg"}}),
Fuzzy = Table.FuzzyNestedJoin(
Sales, {"Product"}, Products, {"Product"}, "Match", JoinKind.LeftOuter,
[Threshold = 0.85, NumberOfMatches = 1, SimilarityColumnName = "Similarity"]
),
Expanded = Table.ExpandTableColumn(Fuzzy, "Match", {"Product ID", "Product", "Similarity"}, {"Product ID", "Matched Product", "Similarity"})
in
ExpandedThis block needs Power Query in Excel or Power BI. The browser simulator on this site doesn't run fuzzy matching, so no output is shown and no scores are quoted here. Run it on your own data and read the scores it returns.
Choose the threshold from your own scores
There is no correct threshold in general. Microsoft's example is that "Grapes" matches "Graes" only when the threshold is below 0.90. A practical way to choose:
- Run the merge with a lower threshold than you expect to keep, for example 0.70 (the 0.85 in the example above is only an illustration),
NumberOfMatches = 1and a similarity column. - Sort by similarity, lowest first, and read down the list.
- Note the score at which wrong matches stop appearing. Set the threshold just above it.
Expect a grey zone where right and wrong matches have similar scores. Short names are the hardest: two short product codes can differ by one character and still be different products. That is why the next step matters.
Number of matches and the many-to-many trap
Without NumberOfMatches, one sales row can match two similar products, and expanding the result duplicates the sales row. Your totals then go up without any error. Set the number of matches to 1, then check the row count before and after the merge. The method in finding where rows go missing, counting rows after every step, catches a rise as well as a drop. The lesson on predicting row counts after a merge explains how to spot this.
Lock confirmed matches into an alias table
A fuzzy merge recalculates its matches on every refresh. If the reference list gains a new product with a similar name, an old row can quietly switch to it. For reporting, you usually want a match that someone has checked once and that never changes.
The fix is to use the fuzzy merge as a review tool, and to keep the answers in a two-column alias table (a mapping table of known spellings). Each checked pair becomes a row: the messy key in From, the correct key in To. The main query then uses exact merges only:
let
Sales = #table({"Order", "Key"}, {
{1, "OLIVE OIL 500ML"}, {5, "OLVE OIL 500ML"}, {6, "SEA SALT AND PEPPER 1KG"}, {8, "BALSAMIC VINEGAR 250ML"}
}),
Products = #table({"Product ID", "Key", "Product"}, {
{"P-01", "OLIVE OIL 500ML", "Olive Oil 500 ml"},
{"P-02", "SEA SALT AND PEPPER 1KG", "Sea Salt and Pepper 1 kg"}
}),
Aliases = #table({"From", "To"}, {{"OLVE OIL 500ML", "OLIVE OIL 500ML"}}),
WithAlias = Table.NestedJoin(Sales, {"Key"}, Aliases, {"From"}, "Alias", JoinKind.LeftOuter),
Resolved = Table.AddColumn(WithAlias, "Match Key", each if Table.IsEmpty([Alias]) then [Key] else [Alias]{0}[To], type text),
Matched = Table.NestedJoin(Table.RemoveColumns(Resolved, {"Alias"}), {"Match Key"}, Products, {"Key"}, "P", JoinKind.LeftOuter),
Out = Table.ExpandTableColumn(Matched, "P", {"Product ID", "Product"})
in
OutResult
| Order | Key | Match Key | Product ID | Product |
|---|---|---|---|---|
| 1 | OLIVE OIL 500ML | OLIVE OIL 500ML | P-01 | Olive Oil 500 ml |
| 5 | OLVE OIL 500ML | OLIVE OIL 500ML | P-01 | Olive Oil 500 ml |
| 6 | SEA SALT AND PEPPER 1KG | SEA SALT AND PEPPER 1KG | P-02 | Sea Salt and Pepper 1 kg |
| 8 | BALSAMIC VINEGAR 250ML | BALSAMIC VINEGAR 250ML | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Orders 1 and 5 both resolve to P-01, Olive Oil 500 ml, and order 6 to P-02. Order 8, Balsamic Vinegar, has no product: the result shows null in both product columns. Nothing has been guessed.
The alias table uses the same From and To column names as Power Query's own transformation table, so you can also pass it to a fuzzy merge as the TransformationTable option. In a workbook, keep it on its own sheet so anyone can add a row without opening the editor. The post on cleaning survey multi-select answers uses the same kind of mapping for option labels. The guide on mapping tables versus nested if explains why a table beats a chain of conditions as the list grows.
List the names that still don't match
Every refresh can bring new spellings. A left anti join returns only the rows with no match in the reference list:
let
Sales = #table({"Order", "Key"}, {{1, "OLIVE OIL 500ML"}, {8, "BALSAMIC VINEGAR 250ML"}}),
Products = #table({"Product ID", "Key"}, {{"P-01", "OLIVE OIL 500ML"}}),
Unmatched = Table.RemoveColumns(Table.NestedJoin(Sales, {"Key"}, Products, {"Key"}, "P", JoinKind.LeftAnti), {"P"})
in
UnmatchedResult
| Order | Key |
|---|---|
| 8 | BALSAMIC VINEGAR 250ML |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Only order 8 comes back. Load this query to its own sheet. When it isn't empty, run the fuzzy merge on just these rows to get suggestions, check them, and add the good ones to the alias table. Over time the list of unmatched names shrinks to genuinely new products. The lesson on merge join kinds covers anti joins, and the exercise find orders from unknown customers practises the same pattern.
Cluster values within one column
Sometimes there is no reference list, only one column with many spellings. Power Query Online has Cluster values on the Add column tab, which groups similar values and maps each to the most frequent spelling in its group. Microsoft's cluster values article (Microsoft Learn) notes that the feature is available only in Power Query Online, and that it uses the same threshold, ignore-case and transformation-table options as fuzzy merge.
The output is again a suggestion. Review the clusters, then build your reference list or alias table from the ones you accept.
A workflow that stays correct after refresh
Put together, the approach has four parts, each a separate query:
- Normalise names in both tables with the same function.
- Resolve keys through the alias table, then merge exactly against the reference list.
- List unmatched rows with an anti join.
- Suggest matches for the unmatched rows with a fuzzy merge, for a person to review.
Only steps 1 to 3 feed the report. Step 4 is a tool for the person who maintains the alias table. This keeps the report predictable while still using fuzzy matching where it is strong: finding candidates a person would take much longer to spot. The same thinking applies to customer and company names, which the post on cleaning a messy CRM export covers.
Practise exact merges before fuzzy ones
Fuzzy matching is only as good as the merge around it. The merging tables exercises on Power Query Exercises cover join kinds, composite keys and row-count checks, and every exercise runs in the browser with the expected result to compare against. The inventory and supplier merge project puts them together on a realistic product list.
Frequently asked questions
Does fuzzy merge ignore case?
Yes, by default. In M the IgnoreCase option of Table.FuzzyNestedJoin defaults to true, so "Grapes" matches "grapes". An ordinary exact merge does not ignore case, which is one more reason to upper-case your keys first.
Why does fuzzy merge match the wrong product?
Usually because the threshold is too low for short names, or because several reference names are similar to each other. Raise the threshold, set the number of matches to 1, and move the pairs you have checked into a mapping table.
Can I fuzzy match on more than one column?
The key arguments of Table.FuzzyNestedJoin accept a list of columns, but each must be text and every key is compared fuzzily. When one column should match exactly, such as supplier, fuzzy match on the name only and leave NumberOfMatches unset or small, so each row can get several candidates. Expand the matched supplier, keep the candidates whose supplier is equal, then keep the highest similarity per row. Rows left with no candidate go to the unmatched list.
Sources
- Text.Select, Microsoft Learn (learn.microsoft.com)
- fuzzy merge article (Microsoft Learn) (learn.microsoft.com)
- reference page (Microsoft Learn) (learn.microsoft.com)
- cluster values article (Microsoft Learn) (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Keep or remove leading zeros in Power QueryKeep leading zeros in Power Query by typing codes as text, put lost zeros back with Text.PadStart, and remove them safely with Text.TrimStart.
- Cleaning phone numbers and postcodes with Power QueryStandardise UK phone numbers and postcodes in Power Query: keep only digits, fix +44 and lost leading zeros, re-space postcodes and flag values that fail.
- How to split full names into first and last names in Power QuerySplit a full name column into first and last names in Power Query, with rules for middle names, single names, 'Last, First' order, titles and suffixes.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.