TECH **Power BI: Merging & Appending Queries (Join Types) – Zero-Fluff Study Guide**

Power BI: Merging & Appending Queries (Join Types) – Zero-Fluff Study Guide

1. What This Is & Why It Matters

You’re building a Power BI report for a retail client. They give you: - Sales data (transactions, dates, product IDs, quantities) - Product catalog (product IDs, names, categories, cost prices) - Store locations (store IDs, regions, managers)

Problem: You can’t just dump these tables into Power BI and expect magic. If you don’t merge (join) the sales data with the product catalog, you won’t know what was sold. If you don’t append (stack) last month’s sales with this month’s, you won’t see trends.

Why this matters in production:
- Broken reports: If you merge incorrectly (e.g., Left Outer instead of Inner), you’ll silently drop rows, leading to wrong totals.
- Performance nightmares: A bad join on large tables can turn a 2-second refresh into a 20-minute slog.
- Data integrity risks: If you append mismatched columns, Power BI will either error or create nulls, corrupting your analysis.

Real-world scenario:
You inherit a Power BI file where someone manually copied-pasted Excel sheets into one table. Now, the report is slow, breaks on refresh, and no one trusts the numbers. Your job: Fix it by properly merging and appending queries.

2. Core Concepts & Components

? Merge Queries (Joins)

• Definition: Combines columns from two tables based on a matching key (like SQL joins).
• Production insight: If your key has duplicates (e.g., multiple sales for the same product), a Full Outer join will explode your row count. Always check key uniqueness first.

? Append Queries (Unions)

• Definition: Stacks rows from two tables with the same columns (like SQL UNION ALL).
• Production insight: If column names don’t match, Power BI will create nulls or errors. Rename columns before appending.

? Join Types (Merge Variations)

? Key Column

• Definition: The column(s) used to match rows between tables (e.g., ProductID).
• Production insight: If the key has duplicates, your join will multiply rows. Use Table.Distinct() to clean first.

? Fuzzy Matching

• Definition: Joins tables based on similar text (e.g., "Nike" vs. "NIKE").
• Production insight: Useful for dirty data (e.g., merging customer lists from different systems), but slow on large tables. Test on a sample first.

? Query Folding

• Definition: Power BI pushes transformations back to the data source (e.g., SQL Server) for efficiency.
• Production insight: Merges/appends after filtering will fold better. Always filter before joining.

3. Step-by-Step Hands-On: Merging & Appending in Power BI

Prerequisites

• Power BI Desktop installed.
• Two sample tables (we’ll create them in Power Query).

Task 1: Merge (Join) Sales and Product Data

Goal: Combine sales transactions with product details.

Step 1: Create Sample Data

1. Open Power BI Desktop.
2. Click Home > Enter Data.
3. Create a table named Sales:
   | OrderID | ProductID | Quantity | Date |
|---------|-----------|----------|------------|
| 1 | P100 | 2 | 2023-01-01 |
| 2 | P200 | 1 | 2023-01-02 |
| 3 | P300 | 3 | 2023-01-03 |
4. Create another table named Products:
   | ProductID | ProductName | Category | CostPrice |
|-----------|-------------|----------|-----------|
| P100 | Laptop | Tech | 500 |
| P200 | Mouse | Tech | 20 |
| P400 | Chair | Furniture| 100 |

Step 2: Merge the Tables

1. Go to Home > Transform Data to open Power Query Editor.
2. Select the Sales query.
3. Click Home > Merge Queries > Merge Queries.
4. In the merge dialog:
5. Select Products as the second table.
6. Choose ProductID from both tables.
7. Select Left Outer join (to keep all sales, even if product data is missing).
8. Click OK.
9. A new column Products appears (a "table" column). Click the expand icon (↗) in the column header.
10. Uncheck ProductID (it’s redundant) and click OK.
11. Rename the new columns (e.g., ProductName, Category, CostPrice).

Expected Output:

| OrderID | ProductID | Quantity | Date       | ProductName | Category | CostPrice |
|---------|-----------|----------|------------|-------------|----------|-----------|
| 1       | P100      | 2        | 2023-01-01 | Laptop      | Tech     | 500       |
| 2       | P200      | 1        | 2023-01-02 | Mouse       | Tech     | 20        |
| 3       | P300      | 3        | 2023-01-03 | null        | null     | null      |

Why this works: Left Outer keeps all sales rows, even if ProductID P300 doesn’t exist in Products.

Task 2: Append (Union) Monthly Sales Data

Goal: Combine January and February sales into one table.

Step 1: Create February Data

1. In Power Query, click Home > Enter Data.
2. Create a table named Sales_Feb:
   | OrderID | ProductID | Quantity | Date |
|---------|-----------|----------|------------|
| 4 | P100 | 1 | 2023-02-01 |
| 5 | P400 | 2 | 2023-02-02 |

Step 2: Append the Tables

1. Select the Sales query.
2. Click Home > Append Queries > Append Queries as New.
3. Select Two tables.
4. Choose Sales_Feb as the second table.
5. Click OK.
6. Rename the new query to Sales_Combined.

Expected Output:

| OrderID | ProductID | Quantity | Date       |
|---------|-----------|----------|------------|
| 1       | P100      | 2        | 2023-01-01 |
| 2       | P200      | 1        | 2023-01-02 |
| 3       | P300      | 3        | 2023-01-03 |
| 4       | P100      | 1        | 2023-02-01 |
| 5       | P400      | 2        | 2023-02-02 |

Why this works: Both tables have the same columns, so Power BI stacks them.

4. ? Production-Ready Best Practices

Performance

• Filter before merging: If you only need 2023 sales, filter before joining to Products. This reduces the data volume early.
• Avoid Full Outer joins: They create massive tables. Use Left Outer or Inner instead.
• Disable "Enable Load" for intermediate tables: Right-click a query > Enable Load (off). This prevents Power BI from loading unnecessary tables into the model.

Data Integrity

• Check for duplicates: Before merging, add a step to remove duplicates in the key column: powerquery = Table.Distinct(#"Previous Step", {"ProductID"})
• Handle nulls: After a merge, replace nulls with defaults (e.g., 0 for CostPrice): powerquery = Table.ReplaceValue(#"Merged", null, 0, Replacer.ReplaceValue, {"CostPrice"})
• Validate joins: After merging, add a custom column to flag unmatched rows: powerquery = if [ProductName] = null then "Missing Product" else "OK"

Maintainability

• Name steps clearly: Instead of "Merged Queries," use "Merge Sales with Products (Left Outer)."
• Document assumptions: Add a comment in the query: powerquery // Left Outer join to keep all sales, even if product data is missing.
• Use parameters for dynamic joins: If your join key changes (e.g., ProductID vs. SKU), use a parameter to avoid hardcoding.

Observability

• Log row counts: Add a step to count rows before/after merging: powerquery = Table.AddColumn(#"Previous Step", "RowCount", each Table.RowCount(#"Previous Step"))
• Monitor refresh times: If a merge takes >30 seconds, consider:
• Moving the join to the data source (e.g., SQL view).
• Pre-aggregating data.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. "Which join type keeps all rows from the left table?"
2. Answer: Left Outer.

3. Trap: Confusing Left Outer with Right Outer.

4. "You have two tables with different column names. What happens when you append them?"

5. Answer: Power BI creates nulls for mismatched columns.

6. Trap: Assuming it will error (it won’t, but it’s still wrong).

7. "How do you merge tables when the key has duplicates?"

8. Answer: Remove duplicates first with Table.Distinct().

9. Trap: Suggesting Group By (overkill for this case).

10. "Which join type is most performant for large tables?"

11. Answer: Inner Join (smallest result set).
12. Trap: Defaulting to Full Outer.

Key ⚠️ Trap Distinctions

Scenario-Based Question

"You need to combine two tables where 10% of the keys don’t match. Which join type should you use?"
- Answer: Left Outer (if you need all rows from the left table) or Full Outer (if you need all rows from both).
- Why not Inner? It would drop the 10% unmatched rows, losing data.

7. ? Hands-On Challenge (with Solution)

Challenge

You have two tables: - Employees (columns: EmployeeID, Name, Department) - Salaries (columns: EmpID, Salary, Bonus)

Task:
1. Merge the tables to show all employees (even those without salaries).
2. Replace null Salary values with 0.

Solution

1. In Power Query, select Employees.
2. Click Merge Queries > Merge Queries.
3. Select Salaries as the second table.
4. Choose EmployeeID (left) and EmpID (right).
5. Select Left Outer join.
6. Expand the Salaries column, uncheck EmpID.
7. Add a step to replace nulls:
   powerquery
= Table.ReplaceValue(#"Expanded Salaries", null, 0, Replacer.ReplaceValue, {"Salary", "Bonus"})

Why it works:
- Left Outer keeps all employees.
- Table.ReplaceValue handles nulls.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Microsoft Docs: Merge Queries – Official guide with examples.
2. Power BI Guided Learning: Combining Data – Free interactive tutorial.
3. SQLBI: Optimizing Merges – Deep dive on performance.
4. Guy in a Cube: Merge vs. Append – 10-minute video breakdown.