📎 This article includes 3 downloadable practice files ↓
Merge Queries has six join kinds. Most people only use the default, Left Outer. The two “anti” joins are the ones accountants need.
| Join kind | Keeps | Question it answers |
|---|---|---|
| Left Outer | All left rows, matching right | Lookup (like XLOOKUP) |
| Inner | Only matches | Invoices that were paid |
| Left Anti | Left rows with no match | Invoices with no payment |
| Right Anti | Right rows with no match | Payments with no invoice |
| Full Outer | Everything | Complete reconciliation view |
Unpaid = Table.NestedJoin(Invoices, {"Invoice"}, Payments, {"Invoice"}, "P", JoinKind.LeftAnti),
Result = Table.RemoveColumns(Unpaid, {"P"})In the practice data the left anti join finds 10 unpaid invoices, and the reverse check finds 1 payment (INV-9999) that matches no invoice: exactly the kind of thing an auditor asks about.
Common mistakes
- Key columns of different types (text “3001” vs number 3001) match nothing. Set both to the same type before merging.
- Trailing spaces in keys: Trim both columns first.
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 2 M solutionThe full query. Paste into Advanced Editor and change the Folder line.⬇ PQ · 1,020 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.