📎 This article includes 3 downloadable practice files ↓
Names typed by hand never match exactly. Fuzzy merge compares how similar two texts are and matches above a threshold (0 to 1).
In the Merge dialog tick Use fuzzy matching > Fuzzy matching options: Similarity threshold 0.6, Ignore case, Match by combining text parts. In M:
Table.FuzzyNestedJoin(Orders, {"Customer"}, Master, {"Customer"}, "M", JoinKind.LeftOuter,
[IgnoreCase = true, IgnoreSpace = true, Threshold = 0.6])| Typed | Matched (0.6) |
|---|---|
| sharma traders | Sharma Traders |
| Sharma Trader | Sharma Traders |
| Mehta and Sons | Mehta & Sons |
| Iyer Store | Iyer Stores |
| Das & Company | no match |
| Gupta Agencies | no match (not in master) |
“Das & Company” vs “Das & Co” is too different at 0.6. Lowering the threshold catches it, but also risks wrong matches. The better fix is a transformation table (Fuzzy matching options): a two-column list “From / To” of known variations, applied before the similarity check.
Practice
Unzip the source files (e.g. to D:\PQ\). Try to build the query with the ribbon first, then compare with the M solution and the expected result. Every solution was run by Excel’s own Power Query engine before publishing.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 8 KB)
- 🗂️Practice source files (zip)All messy CSVs for the course: invoices, payments, typed customer names, credit history, contacts, branch files, a two-row-header report and an ERP register.⬇ ZIP · 3 KB
- 📄Lesson 3 M solutionThe full query. Paste into Advanced Editor and change the Folder line.⬇ PQ · 908 B
- 📗Expected resultWhat the query returned when Excel ran it, to compare with yours.⬇ XLSX · 5 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.