10.5 Merge Queries
Merge queries (Left Outer) with example — in 30 seconds
What: Merge joins two queries on a matching column, like XLOOKUP but inside Power Query.
Why: The join is saved as a step. Next month you press Refresh and the manager names come again.
Tiny example: Orders and Stores are merged on Store ID. BLK-PUN-09 is missing from Stores, so its manager is null.
Merge queries (Left Outer) with example — 30 सेकंदात
काय: Merge दोन queries एका जुळणाऱ्या column वर जोडतो, XLOOKUP सारखं पण Power Query मध्ये.
का: Join एक step म्हणून save होतो. पुढच्या महिन्यात Refresh दाबलं की manager ची नावं परत येतात.
छोटं उदाहरण: Orders आणि Stores Store ID वर merge होतात. BLK-PUN-09 Stores मध्ये नाही, म्हणून त्याचा manager null.
Merge queries (Left Outer) with example — 30 सेकंड में
क्या: Merge दो queries को एक मेल खाते column पर जोड़ता है, XLOOKUP जैसा पर Power Query के अंदर.
क्यों: Join एक step की तरह save होता है. अगले महीने Refresh दबाते ही manager के नाम फिर आ जाते हैं.
छोटा उदाहरण: Orders और Stores Store ID पर merge होते हैं. BLK-PUN-09 Stores में नहीं है, इसलिए उसका manager null है.
First look at the data (before → after):
Expected result — Orders with City Manager
| Order ID | Store ID | Amount | City Manager |
|---|---|---|---|
| BLK-1101 | BLK-PUN-01 | 64 | Ravindra Bagale |
| AMN-1102 | AMN-SLP-01 | 1299 | Salman |
| BLK-1103 | BLK-PUN-09 | 150 | null |
About this data: Made-up QuickCart order IDs matched to a Pune rider master. Fictional names and cities.
Before table and formula as text
Before — Query: Orders
| Order ID | Store ID | Amount |
|---|---|---|
| BLK-1101 | BLK-PUN-01 | 64 |
| AMN-1102 | AMN-SLP-01 | 1299 |
| BLK-1103 | BLK-PUN-09 | 150 |
About this data: Left and right sample tables before a Merge — teaching data only.
Before — Query: Stores (part)
| Store ID | City Manager |
|---|---|
| BLK-PUN-01 | Ravindra Bagale |
| BLK-PUN-02 | Shraddha Bagale |
| AMN-SLP-01 | Salman |
About this data: Merge result preview with matching keys. Not real customer data.
Formula / click path
Home › Merge Queries › Orders + Stores on Store ID
Join Kind: Left Outer · expand City Manager
Do it step by step:
- Select the Orders query and click Home › Merge Queries.
- Click Store ID in Orders, choose Stores below, and click Store ID there.
- Keep Join Kind: Left Outer and click OK.
- Click the expand icon on the new column and tick only City Manager.
- Filter for null to find stores missing from the master.
स्टेप बाय स्टेप करून बघा:
- Orders query select करा आणि Home › Merge Queries वर click करा.
- Orders मधल्या Store ID वर click करा, खाली Stores निवडा, आणि तिथल्या Store ID वर click करा.
- Join Kind: Left Outer तसंच ठेवा आणि OK करा.
- नव्या column वरच्या expand icon वर click करा आणि फक्त City Manager tick करा.
- Master मध्ये नसलेली stores शोधायला null filter करा.
स्टेप बाय स्टेप करके देखें:
- Orders query select करें और Home › Merge Queries पर click करें.
- Orders में Store ID पर click करें, नीचे Stores चुनें, और वहाँ के Store ID पर click करें.
- Join Kind: Left Outer वैसा ही रखें और OK करें.
- नए column के expand icon पर click करें और सिर्फ़ City Manager tick करें.
- Master में न होने वाले stores ढूँढने के लिए null filter करें.
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 |
About this data: Join-kind reminder table (Left Outer, Inner, etc.) with short plain-English meanings.
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 करो.
Worked example — add city names to QuickCart orders with Merge
Why
Your Orders sheet has RiderID but not City. A second Riders sheet has the city. Merging saves a slow VLOOKUP copy-paste every morning.
What
Power Query Merge joins two queries on a key column (like SQL join / XLOOKUP for tables).
How
- Load Orders (OrderID, RiderID, Amount ₹) and Riders (RiderID, RiderName, City) as two queries.
- On Orders choose Home → Merge Queries.
- Pick Riders as the second table. Click RiderID on both sides.
- Join Kind: Left Outer (keep all orders). OK.
- Expand the new column → tick RiderName and City → OK → Close & Load.
Formula
Merge Orders ⋈ Riders on RiderID (Left Outer)Meaning: Left Outer Merge = keep all left rows, bring matching columns from the right. Same idea as XLOOKUP down a column, but for whole tables.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Merge Orders to Riders on RiderID with Left Outer.
Hint: Home → Merge Queries. Select the same key on both sides.
2. Show only matching rows (Inner join).
Hint: Change Join Kind to Inner. Rows without a match disappear.
3. Expand City and RiderName after the merge.
Hint: Click the expand icon on the new column. Untick RiderID if it duplicates.
4. Find orders whose RiderID is missing in Riders (Left Anti).
Hint: Join Kind → Left Anti. Good for data-quality checks.
5. Merge a Prices table (€) to a Berlin store Orders table on SKU.
Hint: Same Merge idea — key is SKU, not RiderID.
6. Why use Merge instead of VLOOKUP for 50,000 rows?
Hint: Think about refresh, steps, and wide tables.
7. After Merge, change a city in Riders and refresh.
Hint: Data → Refresh All. The city on Orders should update.
8. Merge on two keys: City + Product.
Hint: Hold Ctrl to select two columns on each table before Merge.
9. Fuzzy match almost-same names (Rahul / Rahul P).
Hint: Merge dialog → Fuzzy matching (Microsoft 365). Start with a small sample.
10. Keep the merge as a separate query named OrdersWithCity.
Hint: Use Merge Queries as New, then rename in the Queries pane.
11. Add a custom column Amount£ = Amount₹/105 for a UK report.
Hint: Add Column → Custom Column after the merge.
12. Document the join kind in a step note for your team.
Hint: Right-click the Merge step → Properties → Description.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Toronto", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
