Appending queries in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to append the data from Sheet2 below Sheet1 using the correct function.

Excel

=[1](Sheet1!A1:C10, Sheet2!A1:C10)

Drag options to blanks, or click blank then click option'

ASUM

BHSTACK

CFILTER

DVSTACK

Attempts:

3 left

💡 Hint

Common Mistakes

Using HSTACK which stacks data horizontally instead of vertically.

Using SUM which adds numbers instead of appending data.

Using FILTER which filters data but does not append.

The VSTACK function stacks ranges vertically, appending rows from the second range below the first.

2fill in blank

medium

Complete the formula to append data from two tables named Table1 and Table2 using Power Query's function.

Excel

=Table.[1](Table1, Table2)

Drag options to blanks, or click blank then click option'

AAppend

BMerge

CJoin

DCombine

Attempts:

3 left

💡 Hint

Common Mistakes

Using Merge which combines tables side-by-side based on keys.

Using Join which is not a Power Query function.

Using Combine which is not a valid Power Query function.

The Table.Append function in Power Query appends rows from the second table below the first.

3fill in blank

hard

Fix the error in the formula that tries to append ranges but uses the wrong function.

Excel

= [1](A1:B5, A6:B10)

Drag options to blanks, or click blank then click option'

ASUM

BHSTACK

CVSTACK

DCONCAT

Attempts:

3 left

💡 Hint

Common Mistakes

Using HSTACK which stacks data horizontally.

Using SUM which adds numbers instead of appending.

Using CONCAT which joins text strings.

The correct function to append ranges vertically is VSTACK. HSTACK stacks horizontally, SUM adds numbers, and CONCAT joins text.

4fill in blank

hard

Fill both blanks to create a Power Query formula that appends TableA and TableB and then filters rows where the 'Status' column equals 'Active'.

Excel

let appended = Table.[1](TableA, TableB) in Table.SelectRows(appended, each [Status] [2] "Active")

Drag options to blanks, or click blank then click option'

AAppend

B=

C==

DMerge

Attempts:

3 left

💡 Hint

Common Mistakes

Using Merge instead of Append for stacking tables.

Using double equals '==' which is not valid in Power Query.

Confusing the order of arguments in Table.Append.

Table.Append appends the tables. In Power Query, the equality operator is a single = to compare values.

5fill in blank

hard

Fill all three blanks to create a formula that appends two named ranges and then sorts the result by the 'Date' column in descending order.

Excel

let combined = Table.[1](Range1, Range2) in Table.Sort(combined,{{BLANK_2, BLANK_3}})

Drag options to blanks, or click blank then click option'

AAppend

B"Date"

COrder.Descending

DMerge

Attempts:

3 left

💡 Hint

Common Mistakes

Using Merge instead of Append.

Using incorrect syntax for the column name in Table.Sort.

Using ascending order instead of descending.

Table.Append stacks the ranges. "Date" specifies the column to sort by. Order.Descending sorts from newest to oldest.

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