7. Data Cleaning A–Z in Power Query
7.21 Append Queries (Stack Rows)
Append queries in Power Query with example — in 30 seconds
What: Append stacks rows from two tables with the same columns, one under the other.
Why: Blinkit and Amazon Now files have the same columns. You want one Orders table for one report.
Tiny example: 2 Blinkit rows + 2 Amazon Now rows = 4 rows.
Append queries in Power Query with example — 30 सेकंदात
काय: Append एकाच columns असलेल्या दोन tables च्या rows एकाखाली एक लावतो.
का: Blinkit आणि Amazon Now च्या files चे columns सारखेच आहेत. एका report साठी एकच Orders table हवा.
छोटं उदाहरण: 2 Blinkit rows + 2 Amazon Now rows = 4 rows.
Append queries in Power Query with example — 30 सेकंड में
क्या: Append एक जैसे columns वाली दो tables की rows एक के नीचे एक लगाता है.
क्यों: Blinkit और Amazon Now की files के columns एक जैसे हैं. एक report के लिए एक ही Orders table चाहिए.
छोटा उदाहरण: 2 Blinkit rows + 2 Amazon Now rows = 4 rows.
First look at the data (before, then after):
Same before / after tables as text
Before — Blinkit (2 rows)
| Order ID | City | Amount (₹) |
|---|---|---|
| BLK-1 | Pune | 500 |
| BLK-2 | Nashik | 300 |
About this data: This table uses columns: Order ID, City, Amount (₹). This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Before — Amazon Now (2 rows)
| Order ID | City | Amount (₹) |
|---|---|---|
| AMN-1 | Pune | 700 |
| AMN-2 | Mumbai | 400 |
Formula / click path
Home › Append Queries as New › Blinkit + Amazon Now
After — All Orders (4 rows, ₹1,900)
| Order ID | City | Amount (₹) |
|---|---|---|
| BLK-1 | Pune | 500 |
| BLK-2 | Nashik | 300 |
| AMN-1 | Pune | 700 |
| AMN-2 | Mumbai | 400 |
Do it step by step:
- Make sure both queries have the same column names (case and spaces too).
- Click Home › Append Queries › Append Queries as New.
- Choose Blinkit as the first table and Amazon Now as the second.
- Rename the new query to
All Orders. - Right-click the two source queries › untick Enable load.
एक-एक step करून बघा:
- दोन्ही queries मध्ये columns ची नावं सारखी आहेत ना ते तपासा (case आणि spaces सुद्धा).
- Home › Append Queries › Append Queries as New वर click करा.
- पहिला table Blinkit आणि दुसरा Amazon Now निवडा.
- नवीन query चं नाव
All Ordersकरा. - दोन source queries वर right-click करा › Enable load untick करा.
एक-एक step करके देखिए:
- दोनों queries में columns के नाम एक जैसे हैं या नहीं, जाँचिए (case और spaces भी).
- Home › Append Queries › Append Queries as New पर click कीजिए.
- पहली table Blinkit और दूसरी Amazon Now चुनिए.
- नई query का नाम
All Ordersरखिए. - दोनों source queries पर right-click कीजिए › Enable load untick कीजिए.
Append stacks tables on top of each other, like SQL UNION ALL. Columns are matched by name.
Start with a problem (append)
Ruhi Bagale receives two fictional exports for a Pune training lab: Blinkit orders and Amazon Now orders with different column names (Order_No, Order_Value). She needs one Orders table. Append stacks rows; wrong names create half-empty columns.
Show the data (append)
Practice files: src/practice/merge_append_blinkit.csv, merge_append_amazon.csv.
Checkpoint — append
After append you must see 3 rows and one Platform column with Blinkit / Amazon Now. If Order ID is blank on Amazon rows, you appended before renaming.
Fix common mistakes (append)
| Wrong result | Reason | Correction |
|---|---|---|
| Two amount columns, half null | Names differed | Rename before append |
| Helper queries clutter model | Enable load left on | Untick Enable load on sources |
Practise independently (append)
Lab — append practice files
Append the two practice CSVs after renaming. Confirm 3 rows. Hints: rename Order_No, then Order ID, Order_Value, then Amount; add Platform.
Solution
Orders = Table.Combine({Blinkit, Amazon}) after identical headers. Totals Amount = 320+85+410 = 815. See ANSWERS.md.
Think like a professional (append)
Same grain (order line) and same types before combine. Document Platform as a column, not as two models.
Before – two tables
| Order ID | Amount | Platform |
|---|---|---|
| BLK-10001 | 320 | Blinkit |
| BLK-10002 | 85 | Blinkit |
| Order_No | Order_Value |
|---|---|
| AMZ-90001 | 410 |
After – Orders
| Order ID | Amount | Platform |
|---|---|---|
| BLK-10001 | 320 | Blinkit |
| BLK-10002 | 85 | Blinkit |
| AMZ-90001 | 410 | Amazon Now |
Steps in Power BI
- In AmazonNow_Orders, rename
Order_No→Order IDandOrder_Value→Amount(double-click the header). - Add the missing Platform column: Add Column › Custom Column › name
Platform› formula"Amazon Now"(and"Blinkit"in the Blinkit query if needed). - Home › Append Queries › Append Queries as New › Two tables (or Three or more tables) › choose Blinkit_Orders and AmazonNow_Orders › OK.
- Rename the result Orders. Right-click both source queries › untick Enable load.
Orders = Table.Combine({Blinkit_Orders, AmazonNow_Orders})
Ravindra Bagale's Tip
Friends, pay attention: appending before renaming is a classic mistake: "Order_No" and "Order ID" become two half-empty columns, because append matches columns by name. Make column names and types identical in every table before you append, then check that no column is mostly null. It's very simple – just make it a habit.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्ष द्या: rename करण्याआधी append करणं ही classic चूक आहे: "Order_No" आणि "Order ID" दोन अर्धवट रिकामे columns बनतात, कारण append columns नावाने जुळवतो. Append करण्याआधी प्रत्येक table मध्ये column names आणि types एकसारखे करा, मग कोणताही column बहुतांश null नाही ना ते चेक करा. एकदम simple आहे, फक्त सवय लावून घ्या.
Ravindra Bagale's Tip – हिंदी
दोस्तों, ध्यान दो: rename करने से पहले append करना classic गलती है: "Order_No" और "Order ID" दो आधे-खाली columns बन जाते हैं, क्योंकि append columns को नाम से मिलाता है. Append से पहले हर table में column names और types एक जैसे करो, फिर check करो कि कोई column ज़्यादातर null तो नहीं. बहुत आसान है, बस आदत बना लो.
Practice task
Append the Blinkit and Amazon Now DarkStore lists into one table. Add a Platform column first, then remove duplicates by Store ID.
Revision card
Comparison card