Appending queries in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does 'Appending queries' mean in Excel Power Query?

Appending queries means combining two or more tables by stacking them one below the other, like adding more rows.

beginner

How do you start appending queries in Excel Power Query?

In Power Query Editor, go to the Home tab and click 'Append Queries'. Then choose the tables you want to combine.

beginner

True or False: When appending queries, the columns must have the same names and order to combine correctly.

False. Columns are matched by name, not order. If a column is missing in one table, its cells will be blank in the result.

intermediate

What happens if the tables you append have different columns?

Power Query adds all columns from both tables. Missing values in any table show as blank cells in the appended result.

intermediate

Can you append more than two queries at once in Excel Power Query?

Yes, you can append two or more queries at once by selecting 'Three or more tables' in the Append Queries dialog.

What does appending queries do in Excel Power Query?

A Filters rows based on conditions

B Combines tables side by side by columns

C Stacks tables one below the other by rows

D Sorts data alphabetically

Appending queries stacks tables vertically, adding rows from one table below another.

Where do you find the 'Append Queries' option in Excel Power Query?

A Data tab in Excel main window

B Home tab in Power Query Editor

C Insert tab in Excel

D View tab in Power Query Editor

The 'Append Queries' button is on the Home tab inside the Power Query Editor window.

If two tables have different columns, what does Power Query do when appending?

A Adds all columns from both tables, filling blanks where missing

B Only keeps columns common to both tables

C Shows an error and stops

D Deletes extra columns from the bigger table

Power Query includes all columns from both tables and fills missing values with blanks.

Can you append more than two tables at once in Power Query?

A No, only two tables at a time

B Only if tables have the same number of rows

C Only if tables have identical columns

D Yes, by selecting 'Three or more tables' option

Power Query allows appending multiple tables at once using the 'Three or more tables' option.

What happens if columns have different names but same data when appending?

A They become separate columns in the result

B Power Query merges them automatically

C Power Query renames columns to match

D The query fails with an error

Columns with different names stay separate, so data appears in different columns after appending.

Explain how to append two queries in Excel Power Query and what to expect in the result.

Think about combining tables vertically and how columns align.

Describe what happens when appending tables with different columns and how Power Query handles missing data.

Consider how Power Query fills gaps when columns don't match.

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