🔀 Combining and Reshaping Data
🔀 Advanced Prep: Combining and Reshaping
In the previous chapter, we learned how to clean a single table of data. But in the real world, your data will be scattered across many different files. You might have one Excel file for Customers and a completely different CSV file for Sales.
Power Query gives you powerful tools to stick these tables together: Merge and Append.
- Append (Adding Rows): Imagine you have a stack of January receipts, and someone hands you a stack of February receipts. You just put the February stack on the bottom of the January stack. You now have a taller stack of receipts. This is Appending.
- Merge (Adding Columns): Imagine you have a list of Customer IDs and their Sales. But you don't know their names. You take a different list that has Customer IDs and Names, and you glue the Name column onto your Sales list by matching the IDs. Your list just got wider. This is Merging.
➕ Append Queries (SQL UNION)
Appending is very simple. It takes two (or more) queries and stacks them vertically.
- When to use: You have 12 separate Excel files, one for each month of the year, and they all have the exact same column headers (Date, Product, Amount). You append them to create one master 12-month table.
🔗 Merge Queries (SQL JOIN)
Merging requires two tables to share a matching column (usually an ID, like CustomerID). You select the matching column in both tables, and Power Query joins them horizontally.
When you merge, you must choose a "Join Kind". If you know SQL, these are exact equivalents:
| Join Kind | What it does | SQL Equivalent |
|---|---|---|
| Left Outer | Keeps everything from the first table, and only pulls in matches from the second table. (The most common!) | LEFT JOIN |
| Right Outer | Keeps everything from the second table, and only pulls in matches from the first table. | RIGHT JOIN |
| Full Outer | Keeps absolutely everything from both tables, even if they don't match. | FULL OUTER JOIN |
| Inner | Only keeps rows where the ID exists in both tables. | INNER JOIN |
| Left Anti | Only keeps rows in the first table that do not exist in the second table. (Great for finding missing data!) | LEFT EXCLUDING JOIN |
| Right Anti | Only keeps rows in the second table that do not exist in the first table. | RIGHT EXCLUDING JOIN |
🔃 Reshaping: Pivot vs Unpivot
Sometimes your data has all the right numbers, but it's physically shaped wrong for Power BI to understand it.
1. Transpose
Transpose is simple: it flips the table completely on its side. Rows become columns, and columns become rows.
2. Unpivot (Wide to Tall)
This is one of the most magical buttons in Power BI!
Often, humans type data in a "Wide" format because it's easy to read. For example, a table with columns for 2021, 2022, and 2023.
Power BI hates wide data. It wants "Tall" data (a single column called Year and a single column called Value).
- Action: Highlight the
2021,2022, and2023columns, right-click, and hit Unpivot Columns. Power BI will instantly collapse them into a tall, machine-readable format!
3. Pivot (Tall to Wide)
The exact opposite of Unpivot. It takes a tall column of repeating categories and spreads them out horizontally into their own individual columns while calculating the math (like an Excel Pivot Table).
🏋️ Practice Drill
Let's try appending some tables!
Task:
// Try answering these:
1. Inside the Power Query Editor, go to "Enter Data" in the Home ribbon to manually type two small tables.
2. **Table 1:** Create a column `ID` (1, 2) and a column `Name` (Alice, Bob). Name the query `Team A`.
3. **Table 2:** Create a column `ID` (3, 4) and a column `Name` (Charlie, Dave). Name the query `Team B`.
4. In the Home ribbon, click **Append Queries as New**.
5. Select `Team A` as the first table and `Team B` as the second table.
💡 Click for Solutions
Did a new query appear containing IDs 1, 2, 3, and 4 all stacked perfectly on top of each other?
If yes, you just successfully Appended two datasets! You can now right-click Team A and Team B and uncheck "Enable Load" so that only the final Appended master table gets loaded into your Power BI model.
← 🧹 Power Query and Data Cleaning | Next Topic → 📐 Data Modelling and Schemas