Merging Data with Joins

Why Do We Need to Merge Data?

In most real-world business scenarios, data isn't stored in one giant table. It's usually split into several logical tables to avoid redundancy. For example:

• A Sales table contains transaction data (like ProductID, CustomerID, SaleDate, Quantity).
• A Products table contains product details (like ProductID, ProductName, Category).
• A Customers table contains customer information (like CustomerID, CustomerName, Region).

If a manager wants to analyze sales by Region or Product Category, they need a way to bring this information together. This is what a merge operation does. It uses a common column (like ProductID) to look up and add columns from one table to another.

The following short video clip clearly distinguishes between merging (adding columns from different tables) and appending (stacking rows from similar tables, which we will cover in a later lesson).

Understanding Joins: The Logic Behind the Merge

Before we perform a merge in Power Query, it's crucial to understand the logic that governs how the tables are combined. This logic is defined by Join Kinds.

In any merge, there are two tables:

1. The Left Table: Your primary table (the one you are adding data to).
2. The Right Table: The lookup table (the one you are getting data from).

Power Query offers six different join kinds, but for most business analytics, you'll primarily use the Left Outer Join.

• Left Outer Join (Most Common): Keeps all rows from the left (primary) table and brings in matching rows from the right table. If a row in the left table has no match in the right table, the new columns will simply be null (blank). This is how Excel's VLOOKUP function works.
• Inner Join: Keeps only the rows that have a match in both tables. This is useful if you want to analyze only the data that is complete across both tables.

The cheat sheet below provides a fantastic visual summary of all join types. For your workshop, you should focus on explaining the Left Outer Join and Inner Join as they cover the vast majority of use cases.

To deepen your understanding so you can explain it clearly, the following article provides excellent conceptual explanations using Venn diagrams.

How to Merge Tables in Power Query: A Step-by-Step Guide

Now let's walk through the practical steps. For this to work, you must first have both tables loaded into the Power Query Editor.

1. Start from your primary ("Left") table.
2. On the Home tab in the Power Query ribbon, click Merge Queries. You have two options:
   • Merge Queries: Adds the merged columns to your current table.
   • Merge Queries as New: Creates an entirely new query with the combined result, leaving your original tables untouched. This is often a safer choice for beginners.
3. The Merge dialog box will appear.

4. Configure the Merge:

   • The top table is your Left table (it's the one you started from).
   • In the dropdown menu, select your Right (lookup) table.
   • Select the common column in both tables that will be used to match the rows (e.g., ProductID). Power Query will give you a helpful message at the bottom telling you how many rows match.
   • Select the Join Kind. For now, we'll use Left Outer (all from first, matching from second).
   • Click OK.

5. Expand the New Column:

   • A new column will be added to your table. The column name will be the name of your right table, and each cell will contain the word Table. This column holds all the data from the right table for each matching row.
   • Click the expand icon (two arrows pointing in opposite directions) in the column header.
   • A dropdown will appear, listing all the columns from your right table. Uncheck the original key column (as you already have it) and any other columns you don't need.
   • It's good practice to uncheck "Use original column name as prefix" to get cleaner column names.
   • Click OK. The columns you selected will be added to your table.

The following video provides a complete demonstration of this process.

Conclusion

You are now equipped to teach managers one of the most powerful data transformation features in Power Query. Merging tables is the key to breaking down data silos and creating the comprehensive datasets needed for meaningful analysis and visualization.

Key Takeaways for Your Workshop:

• Merging is used to add columns from one table to another based on a shared key (common column).
• The Left Outer Join is the most common and functions like an Excel VLOOKUP: it keeps everything from your main table and adds matching data from a second table.
• The process is simple: Merge Queries -> Select tables and keys -> Choose join kind -> Expand the new column to select the fields you want.

Preview of the Next Lesson:
We've just learned how to combine tables horizontally by adding columns. In the next lesson, we will learn how to combine them vertically. We will cover how to append data from multiple sources into a unified dataset, which is perfect for tasks like stacking monthly sales reports into a single year-to-date table.