10.5 Merge Queries
Merge = join two tables on a key column (like XLOOKUP, but for whole tables and many columns).
| Join kind | Returns | Typical use |
|---|---|---|
| Left Outer (default) | All rows from first, matches from second | Add store details to orders |
| Right Outer | All rows from second, matches from first | |
| Full Outer | All rows from both | Reconciliation |
| Inner | Only matching rows | Orders of known stores only |
| Left Anti | Rows in first with no match | Orders with Store ID missing in master |
| Right Anti | Rows in second with no match | Stores with no orders |
Steps in Excel
- Select the
All_Ordersquery › Home › Combine › Merge Queries (or as New). - Top table: All_Orders, click the Store ID column. Bottom table:
Stores, click Store ID. - Join Kind: Left Outer › OK. The status bar shows how many rows matched.
- A new column Stores with Table values appears › click the expand icon (↔) › tick City Manager and Manager Email › untick Use original column name as prefix › OK.
- Data-quality check: merge again with Left Anti to list orders whose Store ID is missing in the master.
- City mapping: merge the City column with the
CityMapquery (5.5) on the lower-case trimmed name.
Ravindra Bagale's Tip
"The rows increased after the merge" – many students experience this. The reason is a duplicate key in the lookup table (the same Store ID twice) – so every order appears twice. Before merging, do Remove Duplicates (on the key column) in the lookup table, and compare the row count after the merge.
Ravindra Bagale's Tip – मराठी
Merge नंतर rows वाढल्या – असा अनुभव बऱ्याच students ना येतो. कारण lookup table मध्ये key duplicate आहे (एकच Store ID दोनदा) – मग प्रत्येक order दोनदा येते. Merge च्या आधी lookup table वर Remove Duplicates (key column) करा, आणि merge नंतर row count compare करा.
Ravindra Bagale's Tip – हिंदी
Merge के बाद rows बढ़ गईं – ऐसा बहुत से students के साथ होता है. वजह है lookup table में duplicate key (एक ही Store ID दो बार) – फिर हर order दो बार आता है. Merge से पहले lookup table पर Remove Duplicates (key column) करो, और merge के बाद row count compare करो.
Practice task
Merge All_Orders with Stores (Left Outer) to add City Manager, then run a Left Anti merge to find unknown Store IDs. Merge with Products to add Category.