Appending queries in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a combined sales report for two different regions to see total sales in one place.

📊 Data: You have two tables: one with sales data from the East region and one from the West region. Each table has columns for Date, Product, and Sales Amount.

🎯 Deliverable: Create a single combined table that appends the East and West region sales data together.

Sample Data

Date	Product	Sales Amount
2024-01-01	Apples	100
2024-01-02	Bananas	150
2024-01-03	Cherries	200

East Region Sales

Date	Product	Sales Amount
2024-01-01	Apples	100
2024-01-02	Bananas	150
2024-01-03	Cherries	200

West Region Sales

Date	Product	Sales Amount
2024-01-01	Apples	120
2024-01-02	Bananas	130
2024-01-04	Dates	180

1

Step 1: Open Excel and create a new worksheet named 'Combined Sales'.

Expected Result

A blank worksheet named 'Combined Sales' is ready.

2

Step 2: Copy the East Region Sales data (Date, Product, Sales Amount) and paste it starting at cell A1 in the 'Combined Sales' sheet.

Expected Result

East Region Sales data appears in cells A1:C4 with headers in row 1.

3

Step 3: Below the East Region data, paste the West Region Sales data starting at cell A5, including headers.

Expected Result

West Region Sales data appears in cells A5:C8, headers repeated in row 5.

4

Step 4: Remove the duplicate headers in row 5 by deleting the cells A5:C5 so only data remains.

Expected Result

Combined data with one header row at A1:C1 and all sales data below without repeated headers.

5

Step 5: Select the combined data range including headers and sales rows, then create an Excel Table by pressing Ctrl+T and confirming the range with 'My table has headers' checked.

Expected Result

Data is formatted as an Excel Table for easy filtering and analysis.

6

Step 6: Add a new column named 'Region' to the right of the Sales Amount column in the table.

In the first cell under 'Region', enter the formula: =IF(ROW()-ROW(Table1[#Headers])<=3,"East","West")

Expected Result

The 'Region' column fills with 'East' for the first 3 rows and 'West' for the next 3 rows.

7

Step 7: Use the combined table to analyze total sales by region or product as needed.

Use a PivotTable: Rows=Region, Columns=Product, Values=SUM of Sales Amount

Expected Result

PivotTable shows total sales for each product by region.

Final Result

Combined Sales Table
+------------+----------+--------------+--------+
| Date       | Product  | Sales Amount | Region |
+------------+----------+--------------+--------+
| 2024-01-01 | Apples   | 100          | East   |
| 2024-01-02 | Bananas  | 150          | East   |
| 2024-01-03 | Cherries | 200          | East   |
| 2024-01-01 | Apples   | 120          | West   |
| 2024-01-02 | Bananas  | 130          | West   |
| 2024-01-04 | Dates    | 180          | West   |
+------------+----------+--------------+--------+

PivotTable Summary
+--------+---------+---------+-------+
| Region | Apples  | Bananas | Dates |
+--------+---------+---------+-------+
| East   | 100     | 150     |       |
| West   | 120     | 130     | 180   |
+--------+---------+---------+-------+

✓East region sold more Cherries but no Dates.

✓West region sold Dates but fewer Bananas than East.

✓Apples sales are similar in both regions.

Bonus Challenge

Create a dynamic combined table using Power Query to automatically append East and West sales data when updated.

Show Hint

Use Excel's Data > Get & Transform > Get Data > Combine Queries > Append to merge tables dynamically.

Practice

1. What does appending queries do in Excel Power Query?

easy

A. Filters rows based on conditions

B. Joins tables side-by-side to combine columns

C. Stacks tables vertically to combine rows

D. Sorts data alphabetically

Solution

1. Step 1: Understand appending queries

   Appending means stacking tables one on top of another, combining rows from each table.

2. Step 2: Differentiate from other operations

   Joining combines columns side-by-side, filtering removes rows, sorting arranges data. Only appending stacks rows.

3. Final Answer:

   Stacks tables vertically to combine rows -> Option C

4. Quick Check:

   Appending = stacking rows [OK]

Hint: Appending stacks rows; joining stacks columns [OK]

Common Mistakes:

• Confusing appending with joining tables
• Thinking appending filters or sorts data
• Assuming appending merges columns

2. Which of these is the correct way to append two queries named Sales2023 and Sales2024 in Power Query formula language?

easy

A. Table.Combine({Sales2023, Sales2024})

B. Table.Append(Sales2023, Sales2024)

C. Append.Tables(Sales2023, Sales2024)

D. Combine.Tables(Sales2023, Sales2024)

Solution

1. Step 1: Recall Power Query append syntax

   The correct function to append tables is Table.Combine which takes a list of tables.

2. Step 2: Check each option

   Table.Combine({Sales2023, Sales2024}) uses Table.Combine({Sales2023, Sales2024}) which is correct syntax. Others use incorrect function names or argument formats.

3. Final Answer:

   Table.Combine({Sales2023, Sales2024}) -> Option A

4. Quick Check:

   Use Table.Combine with list of tables [OK]

Hint: Use Table.Combine with curly braces for multiple tables [OK]

Common Mistakes:

• Using Table.Append which is not a valid function
• Missing curly braces around tables list
• Confusing function names like Append.Tables

3. Given two tables in Power Query:
Table1 = Table.FromRecords({[ID=1, Name="Anna"], [ID=2, Name="Ben"]})
Table2 = Table.FromRecords({[ID=3, Name="Cara"], [ID=4, Name="Dan"]})
What is the result of Table.Combine({Table1, Table2})?

medium

A. A table with 2 rows: Anna, Ben

B. An error because tables have different rows

C. A table with 2 rows: Cara, Dan

D. A table with 4 rows: Anna, Ben, Cara, Dan

Solution

1. Step 1: Understand Table.Combine behavior

   Table.Combine stacks rows from both tables into one table.

2. Step 2: Count rows after combining

   Table1 has 2 rows (Anna, Ben), Table2 has 2 rows (Cara, Dan). Combined table has 4 rows total.

3. Final Answer:

   A table with 4 rows: Anna, Ben, Cara, Dan -> Option D

4. Quick Check:

   Appending stacks all rows [OK]

Hint: Appending adds all rows from both tables [OK]

Common Mistakes:

• Thinking only first table rows remain
• Expecting error due to different data
• Confusing append with join

4. You wrote this Power Query formula to append two tables:
Table.Combine(SalesJan, SalesFeb)
But it gives an error. What is the problem?

medium

A. Missing curly braces around tables list

B. Table.Combine cannot append tables

C. Table names must be in quotes

D. Function name should be Append.Tables

Solution

1. Step 1: Check Table.Combine syntax

   Table.Combine expects a list of tables inside curly braces, like {SalesJan, SalesFeb}.

2. Step 2: Identify missing syntax

   The formula lacks curly braces, so it passes two arguments instead of one list, causing error.

3. Final Answer:

   Missing curly braces around tables list -> Option A

4. Quick Check:

   Use curly braces for list input [OK]

Hint: Always wrap tables in curly braces for Table.Combine [OK]

Common Mistakes:

• Omitting curly braces around tables
• Using wrong function names
• Putting table names in quotes

5. You have three monthly sales tables: SalesJan, SalesFeb, and SalesMar. Each has columns Product and Amount. You want a single table with all rows combined. Which Power Query formula correctly appends all three tables?

hard

A. Table.Combine(SalesJan, SalesFeb, SalesMar)

B. Table.Combine({SalesJan, SalesFeb, SalesMar})

C. Table.Append({SalesJan, SalesFeb, SalesMar})

D. Append.Tables(SalesJan, SalesFeb, SalesMar)

Solution

1. Step 1: Recall correct syntax for appending multiple tables

   Table.Combine takes a single argument: a list of tables inside curly braces.

2. Step 2: Evaluate options

   Table.Combine({SalesJan, SalesFeb, SalesMar}) correctly uses Table.Combine({SalesJan, SalesFeb, SalesMar}). Options B, C, and D use wrong syntax or function names.

3. Final Answer:

   Table.Combine({SalesJan, SalesFeb, SalesMar}) -> Option B

4. Quick Check:

   Use Table.Combine with list of tables [OK]

Hint: Use Table.Combine with curly braces for multiple tables [OK]

Common Mistakes:

• Passing tables as separate arguments instead of list
• Using non-existent functions like Table.Append
• Forgetting curly braces