📎 This article includes 3 downloadable practice files ↓
VLOOKUP across 50,000 rows is slow and breaks when columns move. Power Query’s Merge joins two tables on a matching column, like a SQL JOIN, and it’s part of the refreshable steps.
Load both tables
Create a query for pq-05-orders.csv and one for pq-05-products.xlsx. For the lookup table, Close & Load To › Only Create Connection, since you don’t need it on a sheet.
Merge
- Open the Orders query › Home › Merge Queries.
- Second table: Products. Click ProductCode in both tables.
- Join Kind: Left Outer (all orders, matching products). OK.
- A new column of “Table” values appears. Click its expand icon ⇔, tick Product and Category, untick Use original column name as prefix.
Join kinds
| Join | Keeps | Use for |
|---|---|---|
| Left Outer | All rows from the first table | Lookups (the usual one) |
| Inner | Only rows that match in both | Keep matched records only |
| Left Anti | Rows in the first with NO match | Find codes missing from the master |
| Full Outer | Everything from both | Reconciliations |
In the practice data, product P105 is missing from the master, so 21 orders get null Product. A Left Anti merge lists exactly those orders, ready to send to whoever maintains the master.
Fuzzy matching
Tick Use fuzzy matching for names typed slightly differently (“Sharma Traders” vs “Sharma Trader”). Set the similarity threshold carefully and check results; it’s for clean-up, not for accounting matches.
Where beginners go wrong
| Problem | Cause |
|---|---|
| No matches at all | Different types (text “101” vs number 101) or spaces; fix types and Trim |
| Row count grew | Duplicate keys in the lookup table |
| Nulls after expanding | Codes missing from the master; check with Left Anti |
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data › Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
📎 Practice files for this article
- 🧾pq-05-orders.csv180 orders with product codes.⬇ CSV · 8 KB
- 📗pq-05-products.xlsxProduct master (one code is deliberately missing).⬇ XLSX · 5 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 10 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.