Day 6: Merge and Append Queries in Power BI
On Day 6, we explore two crucial Power Query operations: Merge Queries and Append Queries. These allow you to combine data from multiple tables efficiently—either column-wise or row-wise.
1. Merge Queries
Merge Queries is used when you need to combine data from two tables based on a common column. This is similar to performing a join in SQL. It’s a column-wise operation, meaning rows are combined based on matching values in one or more columns.
Types of Joins
Power BI offers 7 types of joins:
-
Inner Join – Fetches only the matched records from both tables.
-
Left Outer Join – Fetches all matched records and unmatched records from the left table.
-
Right Outer Join – Fetches all matched records and unmatched records from the right table.
-
Full Outer Join – Fetches all matched and unmatched records from both tables.
-
Left Anti Join – Fetches only unmatched records from the left table.
-
Right Anti Join – Fetches only unmatched records from the right table.
-
Full Anti Join – Fetches unmatched records from both tables.
Example
Consider two tables:
Table A: 1, 2, 3, 4, 6
Table B: 1, 2, 3, 5, 7, 8
| Join Type | Result |
|---|---|
| Inner | 1, 2, 3 → 3 records |
| Left Outer | 1, 2, 3, 4, 6 → 5 records |
| Right Outer | 1, 2, 3, 5, 7, 8 → 6 records |
| Full Outer | 1, 2, 3, 4, 6, 5, 7, 8 → 8 records |
| Left Anti | 4, 6 → 2 records |
| Right Anti | 5, 7, 8 → 3 records |
| Full Anti | 4, 6, 5, 7, 8 → 5 records |
These joins give flexibility depending on whether you want only matched data or also the unmatched rows from one or both tables.
2. Append Queries
Append Queries is used when you want to combine multiple tables row-wise, stacking them on top of each other.
Key Rules for Appending
-
Column names must match exactly.
-
Data types must be the same.
-
Order of columns does not matter.
-
Number of columns can vary, but only matching columns will combine correctly.
For example, if you have sales data from January in one table and February in another, appending these tables will give you a combined dataset covering both months.
Summary
-
Merge Queries = Column-wise combination based on matching values
-
Append Queries = Row-wise combination to consolidate multiple tables
Mastering these operations ensures that your datasets can be combined and analyzed efficiently, even when the data comes from multiple sources.
