Appending queries in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Appending Two Tables Vertically

You have two tables in Excel: Table1 with 3 rows and Table2 with 2 rows. You want to append Table2 below Table1 using Power Query. Which step correctly appends these tables?

A Use the 'Append Queries' option and select Table1 as the primary table and Table2 as the table to append.

B Use the 'Merge Queries' option to combine Table1 and Table2 side by side.

C Copy Table2 and paste it below Table1 in the worksheet manually.

D Use the 'Append Queries as New' option and select only Table1.

💡 Hint

Appending means stacking tables vertically, not side by side.

To append tables in Power Query, you use the 'Append Queries' feature and select the tables to stack vertically. 'Merge Queries' combines tables side by side, which is not appending.

❓ Function Choice

intermediate

Choosing the Right Power Query Function to Append

Which Power Query function is used internally to append two tables vertically?

A Table.Combine

B Table.Join

C Table.SelectRows

D Table.AddColumn

💡 Hint

Appending means combining rows from tables.

Table.Combine merges multiple tables by stacking their rows, which is the append operation in Power Query.

🎯 Scenario

advanced

Appending Queries with Different Column Names

You have two queries: Query1 has columns 'Name' and 'Age', Query2 has columns 'FullName' and 'Age'. You want to append these queries so that the 'Name' and 'FullName' columns align as one column in the result. What should you do before appending?

A Append the queries directly; Power Query will automatically match columns by meaning.

B Delete the 'FullName' column in Query2 before appending.

C Rename the 'FullName' column in Query2 to 'Name' before appending.

D Merge the queries instead of appending.

💡 Hint

Columns must have the same name to combine their data when appending.

Power Query matches columns by name when appending. Renaming 'FullName' to 'Name' ensures data aligns correctly.

📊 Formula Result

advanced

Result of Appending Queries with Mismatched Columns

You append two queries in Power Query: Query1 has columns A and B; Query2 has columns A and C. What will be the result?

A Power Query will raise an error due to mismatched columns.

B The result will have columns A, B, and C; rows from Query1 will have null in C, rows from Query2 will have null in B.

C The result will only have columns A and B; column C will be dropped.

D The result will only have columns A and C; column B will be dropped.

💡 Hint

Appending combines all columns from both tables, filling missing values with null.

When appending tables with different columns, Power Query includes all columns and fills missing values with nulls.

❓ data_analysis

expert

Analyzing Performance Impact of Appending Large Queries

You have two very large queries with millions of rows each. You append them in Power Query. Which of the following is the most likely impact on performance and memory usage?

A Appending merges data without increasing memory usage significantly.

B Appending only loads the first query into memory until you expand the second.

C Appending automatically compresses data to reduce memory usage.

D Appending doubles the data size in memory, potentially causing slowdowns or crashes.

💡 Hint

Appending stacks all rows, increasing total data size.

Appending large queries combines all rows, increasing memory usage and possibly slowing down processing or causing crashes if memory is insufficient.

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