Power Query Combining Data

Power Query: Combining Data Like a Master Chef 🍳

Imagine you’re a chef with ingredients scattered across different tables in your kitchen. Some tables have vegetables, others have spices, and some have meats. To cook a delicious meal, you need to bring everything together in one place!

That’s exactly what Power Query Combining Data does in Excel. It takes information from different tables and brings them together—either by stacking them on top of each other (Appending) or by matching them side by side (Merging).

The Big Picture

graph TD
    A["Multiple Tables"] --> B{How to Combine?}
    B -->|Stack vertically| C["APPEND"]
    B -->|Match horizontally| D["MERGE"]
    C --> E["One Tall Table"]
    D --> F["One Wide Table"]

Think of it this way:

• APPEND = Stacking books on top of each other 📚
• MERGE = Putting puzzle pieces side by side 🧩

Part 1: Appending Queries

What is Appending?

Appending is like adding more pages to your notebook. You take one table and put another table below it. The tables grow taller, not wider.

Real-Life Example

Imagine you have sales data for each month in separate tables:

January Sales:

February Sales:

After appending, you get ONE table:

All rows are now stacked together!

How to Append in Power Query

1. Open Power Query (Data tab → Get Data → Launch Power Query Editor)
2. Click Home → Append Queries
3. Choose your tables
4. Click OK

That’s it! Your tables are now combined vertically.

Two Ways to Append

Append Two Tables

When you have exactly two tables to combine.

graph TD
    A["Table 1"] --> C["Combined Table"]
    B["Table 2"] --> C

Append Three or More Tables

When you have many tables—like 12 months of sales data!

graph TD
    A["January"] --> D["Combined Table"]
    B["February"] --> D
    C["March"] --> D
    E["...and more"] --> D

Golden Rule for Appending

🎯 The columns must match!

If Table 1 has columns [Name, Age] and Table 2 has [Name, Age, City], Power Query will still combine them, but “City” will be empty for Table 1 rows.

Part 2: Merging Queries

What is Merging?

Merging is like looking up a friend’s phone number in a contact book. You have some information (the name), and you want to find matching information (the phone number) from another list.

The Restaurant Analogy

Imagine you’re at a restaurant:

Your Order Slip:

Menu Reference:

After merging (matching Dish Code), you get:

Now you know what was ordered and how much it costs!

How to Merge in Power Query

1. Open Power Query Editor
2. Select your main table
3. Click Home → Merge Queries
4. Pick the second table
5. Click the matching column in both tables
6. Choose your join type
7. Click OK
8. Expand the new column to see the matched data

The 6 Types of Joins (Merge Types)

This is where the magic happens! Different join types give you different results.

graph TD
    A["Join Types"] --> B["Left Outer"]
    A --> C["Right Outer"]
    A --> D["Full Outer"]
    A --> E["Inner"]
    A --> F["Left Anti"]
    A --> G["Right Anti"]

Let’s understand each one with a simple example:

Students Table:

Scores Table:

1. Left Outer Join (Most Common!)

Keep ALL rows from the left table. Match what you can from the right.

Result:

Cara stays even though Class C has no score!

2. Right Outer Join

Keep ALL rows from the right table. Match what you can from the left.

Result:

Class D appears even though no student is in it!

3. Full Outer Join

Keep EVERYTHING from both tables. Match where possible.

Result:

Everyone and everything is included!

4. Inner Join

Keep ONLY rows that match in BOTH tables.

Result:

Only Amy and Bob appear because their classes exist in both tables!

5. Left Anti Join

Keep rows from the left table that have NO match on the right.

Result:

Cara is the only student without a matching score!

6. Right Anti Join

Keep rows from the right table that have NO match on the left.

Result:

Class D is the only score without a matching student!

Quick Comparison: Append vs Merge

Common Mistakes to Avoid

Mistake 1: Wrong Column Match

When merging, make sure you’re matching the RIGHT columns. Matching “Name” with “ID” won’t work!

Mistake 2: Forgetting to Expand

After merging, you’ll see a column with “Table” in every cell. You need to click the expand button (two arrows icon) to see the actual data!

Mistake 3: Mismatched Data Types

If one table has “123” as text and another has 123 as a number, they won’t match. Convert them to the same type first!

Your Power Query Journey

graph TD
    A["Load Your Tables"] --> B["Choose Combine Method"]
    B --> C{Same Structure?}
    C -->|Yes| D["Use APPEND"]
    C -->|Need to match data| E["Use MERGE"]
    D --> F["Pick Tables to Stack"]
    E --> G["Select Key Column"]
    G --> H["Choose Join Type"]
    F --> I["Done! One Big Table"]
    H --> I

Remember This!

🧠 APPEND = “I want MORE ROWS” (stacking data)

🧠 MERGE = “I want MORE COLUMNS” (matching data)

🧠 Left Outer Join = Most common, keeps all your main data

🧠 Inner Join = Only keeps perfect matches

🧠 Anti Joins = Find what’s MISSING

You Did It!

You now understand the two superpowers of Power Query combining:

1. Appending - Stacking tables vertically like building blocks
2. Merging - Matching tables horizontally like a lookup master

With these skills, you can take data from anywhere and combine it into exactly what you need. No more copy-pasting between spreadsheets!

Go forth and combine your data with confidence! 🚀