Merging Data: Performing Joins with Queries

Introduction

In our previous lesson, you built a solid foundation in transforming data within a single table using Power Query. You learned to clean and shape your datasets by removing columns, filtering rows, and setting correct data types—all recorded as repeatable steps. Now, we'll address a very common and critical challenge for any data professional: what to do when the data you need is split across multiple tables.

This lesson introduces the Merge operation in Power Query. Merging allows you to combine two or more tables by matching rows based on a common column, a process known in database terminology as a "join." This is the modern, automated alternative to using Excel functions like VLOOKUP or INDEX/MATCH. Mastering this skill will enable you to enrich your primary datasets, such as adding customer details to sales records or product descriptions to inventory lists, creating the comprehensive tables needed for powerful analysis and reporting.

1. The Core Concepts: Merging and Keys

Before we perform a merge, it's essential to understand the two core concepts that make it work: the distinction between merging and appending, and the role of a "key" column.

Merge vs. Append: Merging joins tables side-by-side, adding new columns from one table to another. Appending stacks tables on top of each other, adding new rows. Today, we focus exclusively on merging.
The Key: A merge works by finding matching values in a "key" column that exists in both tables. For example, if you have a Sales table and a Customers table, the CustomerID column would be the key that links them.

For a merge to be successful and reliable, your key columns must be clean and consistent.

How to Merge Tables with Power Query

This article from Expertrain provides a clear explanation of the prerequisites for merging. It emphasizes the importance of preparing your data, which builds directly on the skills you learned in the last lesson.

First, read the brief section Merge vs Append to solidify your understanding of the difference. Next, read the section on keys. This explains what a key is and what makes a good one. Finally, and most importantly, read Step 2 — Clean your keys. Pay close attention to the checklist (matching data types, trimming text, etc.). This is the single most important step to prevent errors.

As the article highlights, mismatched data types (e.g., trying to match a number 101 with text "101") or hidden extra spaces are the most common reasons why merges fail. Always ensure your keys are clean before you begin.

2. Performing Your First Merge

With the concepts understood, let's walk through a typical merge operation. The process involves selecting your main table, initiating the merge, choosing the second table, identifying the key columns in both, selecting a "join kind," and finally, expanding the new data.

Here is what the Merge dialog box looks like in Power Query. Familiarize yourself with its layout before watching the video demonstration.

The Merge dialog in Power Query where you select tables, matching key columns, and the type of join.

Now, let's see it in action.

How To Easily Merge Tables With Power Query: Vlookup Alternative

This video from Excel Campus - Jon provides a clear, step-by-step walkthrough of a common merge scenario: adding customer information to an orders table. This is a perfect substitute for a traditional VLOOKUP.

Please watch from the main merge workflow. As you watch, focus on these key moments: Initiating the Merge: The presenter starts in the orders table and selects Merge Queries from the Home tab. Selecting Tables and Keys: He selects the customers table as the second table and then clicks the Customer ID column in both tables to set the key. Choosing the Join Kind: He leaves the default, Left Outer Join, and the dialog box shows how many rows match. Expanding the New Column: After the merge, a new column containing Table objects appears. He clicks the expand icon in the column header to choose which columns to bring into the main table.

The video continues to show how to handle null values that result from non-matching keys and how refreshing the query automatically updates the merged data when the source tables change. This automation is the primary reason Power Query is so much more powerful than manual formulas.

3. Understanding the Different "Join Kinds"

In the previous video, the Left Outer Join was used. This is the most common type of join, but Power Query offers several others, and choosing the right one is crucial for getting the result you need. Understanding these options elevates your data skills, especially for freelance work where you may need to perform complex data validation or filtering.

This next video provides an excellent visual explanation of the six primary join types.

Merging Queries and Understanding Join Kinds in Power Query (Power BI & Excel)

The Go Analytics channel offers a very clear, animated breakdown of what each join type does.

Watch the segment from explaining join kinds. Pay close attention to the Venn diagrams used to illustrate each of the six main types.

To help you remember these, here is a handy cheat sheet. It's a great reference to keep nearby as you work.

A visual summary of the different join types available in Power Query. Source: Gorilla.bi

For your freelance projects, three join types will cover the vast majority of your needs:

Left Outer (All from first, matching from second): This is your "lookup" join. Use it to enrich a primary table with additional data.
- Scenario: You have a Sales table and want to add the Customer City from your Customers table. You want to keep all sales records, even if a customer is missing.
Inner (Only matching rows): Use this to find the intersection of two tables. It keeps only the rows that have a match in both tables.
- Scenario: You have a Sales table and a Products table. You want to create a report of sales for only currently active products listed in the Products table.
Left Anti (Rows only in first): This is a powerful tool for data validation and finding exceptions. It keeps only the rows from the first table that do not have a match in the second.
- Scenario: You want to find data entry errors. Use a Left Anti join between your Sales table and your Customers table to find all sales records that have an invalid CustomerID that doesn't exist in the official Customers list.

4. Common Problems and How to Fix Them

Even with a good understanding of the process, merges can sometimes produce unexpected results. Here are the two most common issues:

You get null values in your newly expanded columns.
- Cause: The key in your first table didn't find a match in the second table.
- Fix: This goes back to cleaning your keys! Check for mismatched data types (e.g., Number vs. Text), extra spaces (TRIM), or different casing (e.g., "CA" vs. "ca"). Using a Left Anti join is a great way to isolate these non-matching rows for investigation.
The number of rows in your table increases after the merge.
- Cause: Your "lookup" table (the second table) has duplicate values in its key column. For example, if you are merging Customers into Sales and your Customers table has two entries for CustomerID 123, every sale for customer 123 will be duplicated in the final output.
- Fix: Go to the query for your second table and use the Remove Duplicates function on the key column before merging.

How to Merge Tables with Power Query

For a quick reference on troubleshooting, the Expertrain article has a concise summary.

Review the list under the section Common merge problems. It provides a great checklist for diagnosing issues.

Conclusion

You have now learned one of the most powerful features in Power Query: merging. You can now go beyond single, flat files and start combining data from multiple sources into a single, cohesive, and analysis-ready table. This process is not only more efficient than traditional formulas but is also fully automated, saving you immense time on repetitive client tasks.

Key Takeaways:

Merge adds columns: Merging combines tables side-by-side based on a shared key.
Clean keys are essential: The most common merge failures are due to inconsistent data types, extra spaces, or case differences in the key columns.
Choose the right join kind: While Left Outer is the most common for lookups, Inner and Left Anti are powerful tools for filtering and data validation.
Troubleshoot systematically: Unexpected nulls point to non-matches, while extra rows point to duplicates in your lookup table.

In our next lesson, we will explore the other side of combining data: Appending. While merging adds columns, appending stacks tables on top of each other, allowing you to consolidate data from multiple files or periods into a single master list.