Merging queries (joins) in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a 'join' when merging queries in Excel?

A join combines rows from two tables based on a related column, like matching names or IDs, to create a single table with combined information.

beginner

Name the four common types of joins used in merging queries.

The four common join types are:
1. Inner Join - keeps only matching rows.
2. Left Join - keeps all rows from the left table and matching rows from the right.
3. Right Join - keeps all rows from the right table and matching rows from the left.
4. Full Outer Join - keeps all rows from both tables, matching where possible.

beginner

How do you perform a merge (join) in Excel using Power Query?

In Power Query:
1. Load both tables.
2. Click 'Merge Queries'.
3. Select the matching columns in each table.
4. Choose the join type (e.g., Inner, Left).
5. Click OK to create the merged table.

beginner

What happens if you use an Inner Join but some rows don't have matches?

Rows without matches in either table are excluded from the result. Only rows with matching keys in both tables appear.

beginner

Why might you use a Left Join instead of an Inner Join?

Use a Left Join when you want to keep all rows from the first (left) table, even if there is no matching data in the second (right) table. This helps keep all your main data and add extra info where available.

Which join type keeps all rows from both tables, matching where possible?

A Full Outer Join

B Left Join

C Right Join

D Inner Join

A Full Outer Join keeps all rows from both tables, matching rows where possible and filling gaps with blanks.

In Excel Power Query, what is the first step to merge two tables?

A Click 'Merge Queries' after loading tables

B Sort the tables alphabetically

C Create a Pivot Table

D Use VLOOKUP formula

You first load both tables into Power Query, then click 'Merge Queries' to start joining them.

What does an Inner Join do with rows that don't have matching keys?

A Moves them to the bottom

B Includes them with blanks

C Duplicates them

D Excludes them

Inner Join excludes rows without matching keys in both tables.

Which join type keeps all rows from the left table and adds matching rows from the right?

A Right Join

B Left Join

C Inner Join

D Full Outer Join

Left Join keeps all rows from the left table and adds matching rows from the right table.

When merging queries, what is the key column used for?

A To sort the data alphabetically

B To format the output

C To match rows between tables

D To calculate totals

The key column is used to find matching rows between the two tables.

Explain how you would merge two tables in Excel using Power Query and describe the difference between Inner Join and Left Join.

Think about which rows stay or go when you pick each join type.

Describe a real-life situation where merging queries with a join would be useful and which join type you would choose.

Imagine you have two lists and want to combine them based on a common ID.

Practice

1. What does merging queries (joins) in Excel usually help you do?

easy

A. Combine data from two tables based on a common value

B. Sort data alphabetically

C. Create charts from data

D. Format cells with colors

Solution

1. Step 1: Understand the purpose of merging queries

   Merging queries means joining data from two tables using a common value to combine related information.

2. Step 2: Identify what merging queries does in Excel

   It helps to combine and compare data easily without manual copying or sorting.

3. Final Answer:

   Combine data from two tables based on a common value -> Option A

4. Quick Check:

   Merging queries = Combine data [OK]

Hint: Merging joins tables by matching common values [OK]

Common Mistakes:

• Confusing merging with sorting
• Thinking it creates charts
• Assuming it changes cell colors

2. Which of these is the correct syntax to merge two tables using XLOOKUP in Excel?

easy

A. =XLOOKUP(A2, Table2[Value], Table2[ID])

B. =XLOOKUP(Table2[Value], A2, Table2[ID])

C. =XLOOKUP(Table2[ID], Table2[Value], A2)

D. =XLOOKUP(A2, Table2[ID], Table2[Value])

Solution

1. Step 1: Recall XLOOKUP syntax

   XLOOKUP(lookup_value, lookup_array, return_array) finds lookup_value in lookup_array and returns corresponding value from return_array.

2. Step 2: Match syntax to merging tables

   We want to find A2 (key) in Table2[ID] and return Table2[Value], so =XLOOKUP(A2, Table2[ID], Table2[Value]) is correct.

3. Final Answer:

   =XLOOKUP(A2, Table2[ID], Table2[Value]) -> Option D

4. Quick Check:

   XLOOKUP syntax = lookup_value, lookup_array, return_array [OK]

Hint: XLOOKUP: lookup_value, lookup_array, return_array order [OK]

Common Mistakes:

• Swapping lookup_array and return_array
• Using wrong column references
• Putting return_array before lookup_array

3. Given these tables:

Table1:
ID | Name
1 | Alice
2 | Bob

Table2:
ID | Score
1 | 85
3 | 90

If you use =XLOOKUP(A2, Table2[ID], Table2[Score], "Not Found") in Table1 where A2=1, what is the result?

medium

A. 85

B. 90

C. Not Found

D. 1

Solution

1. Step 1: Understand the lookup

   The formula looks for A2=1 in Table2[ID]. Table2 has ID 1 with Score 85.

2. Step 2: Return the matching Score

   Since ID 1 is found, XLOOKUP returns 85 from Table2[Score].

3. Final Answer:

   85 -> Option A

4. Quick Check:

   Lookup 1 returns 85 [OK]

Hint: XLOOKUP returns matching value or fallback if none [OK]

Common Mistakes:

• Choosing score for ID 3 instead of 1
• Confusing fallback value with result
• Returning ID instead of Score

4. You wrote this formula to merge data:
=XLOOKUP(A2, Table2[Value], Table2[ID])
But it returns wrong results. What is the likely error?

medium

A. Used wrong function name

B. Forgot to lock cell references

C. Swapped lookup_array and return_array columns

D. Used incorrect table name

Solution

1. Step 1: Check XLOOKUP parameters

   lookup_array should be the column with keys to find (e.g., IDs), return_array the column with values to return.

2. Step 2: Identify swapped columns

   Using Table2[Value] as lookup_array and Table2[ID] as return_array is reversed; it should be lookup ID, return Value.

3. Final Answer:

   Swapped lookup_array and return_array columns -> Option C

4. Quick Check:

   Lookup and return arrays must be in correct order [OK]

Hint: Lookup array must be key column, return array the value column [OK]

Common Mistakes:

• Mixing lookup and return columns
• Assuming function name is wrong
• Ignoring cell reference locking

5. You have two tables:

Employees:
ID | Name
101 | John
102 | Mary
103 | Steve

Salaries:
ID | Salary
101 | 5000
103 | 4500

You want to create a merged table showing all employees and their salaries, showing "No Data" if salary is missing. Which formula in cell C2 (next to Name) correctly merges salaries?

hard

A. =XLOOKUP(Name, Salaries[ID], Salaries[Salary], "No Data")

B. =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data")

C. =XLOOKUP(A2, Salaries[Salary], Salaries[ID], "No Data")

D. =XLOOKUP(A2, Employees[ID], Salaries[Salary], "No Data")

Solution

1. Step 1: Identify lookup value and arrays

   We want to find employee ID in Salaries[ID] and return Salaries[Salary]. If not found, show "No Data".

2. Step 2: Check formula correctness

   =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data") uses =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data"), which matches the requirement perfectly.

3. Final Answer:

   =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data") -> Option B

4. Quick Check:

   Lookup ID in Salaries, return Salary or fallback [OK]

Hint: Use XLOOKUP with fallback to handle missing data [OK]

Common Mistakes:

• Using wrong lookup or return columns
• Referencing Name instead of ID
• Mixing tables in lookup arrays