Concept
1. Merge vs Append in one picture
| Merge | Append | |
|---|---|---|
| Direction | adds columns (side by side) | adds rows (stacked) |
| Like | XLOOKUP / VLOOKUP | copy-paste one table under another |
| Needs | a matching key column | the same column names |
| Example | bring Product & Category into Sales | Sales 2024-25 + Sales 2025-26 |
2. Merge: products into sales
Load products.csv as its own query (Products). In the Sales query:
Home → Merge Queries → second table Products → click ProductID in both → Join Kind Left Outer → OK.
= Table.NestedJoin(#"Added Revenue", {"ProductID"}, Products, {"ProductID"}, "Products", JoinKind.LeftOuter)
A new column shows Table in every cell. Click its expand icon ⇄ → tick Product, Category, UnitCost → untick "Use original column name as prefix":
= Table.ExpandTableColumn(#"Merged Queries", "Products", {"Product", "Category", "UnitCost"}, {"Product", "Category", "UnitCost"})
Compared with XLOOKUP on 50,000 rows: no formulas to fill, no recalculation lag, and it's repeated automatically on refresh.
Merge on several columns (e.g. Region + Month for targets): Ctrl + click both columns in the same order in each table.
3. Join kinds
| Join kind | Keeps | Use for |
|---|---|---|
| Left Outer | all rows of the first table + matches | normal lookup (most common) |
| Right Outer | all rows of the second table + matches | rarely |
| Full Outer | everything from both | reconciliation |
| Inner | only rows found in both | keep only valid records |
| Left Anti | first-table rows with no match | find sales with unknown ProductID |
| Right Anti | second-table rows with no match | find products never sold |
Left Anti is a superpower: a duplicate of Sales merged with Products as Left Anti = list of orders with bad product codes — an instant data-quality check. On the capstone data, Left Anti on StoreID before trimming shows the ~1,000 rows with "S01 " style spaces; after trimming it returns zero.
4. Keys must match exactly
- Same data type in both columns (text "101" ≠ number 101).
- Trim spaces; Power Query merges are case-sensitive ("s01" ≠ "S01") — use Format → Uppercase on both if needed.
- The lookup table should have unique keys, or rows multiply (one sale × two matching products = two rows). Check with Remove Duplicates on the key or Group By count.
5. Fuzzy matching
Merge dialog → Use fuzzy matching (Similarity threshold 0.8 etc.) matches "Karol Bagh" with "Karolbagh". Useful for messy names; review results — never trust fuzzy matches for money without checking.
6. Append
Home → Append Queries (or "Append as New") → choose tables:
= Table.Combine({Sales_FY2024_25, Sales_FY2025_26})
Columns are matched by name. A column missing in one table is filled with null; a renamed column ("Amt" vs "Amount") becomes two half-empty columns — rename first.
The From Folder import (Lesson 2) is an automatic append of every file.
7. Performance tips
Merge after filtering and removing columns (fewer rows/columns to match). Keep lookup tables small and clean. For many lookups to many tables, the Data Model relationships in Module 4 are often better than merging everything into one wide table.
Common mistakes
Text vs number keys (no matches, all null). Duplicate keys in the lookup table (row count increases — always compare row counts before/after a merge). Appending tables with different column names.