How to Do a Join in Power BI

Combining data from different spreadsheets or tables is a fundamental skill for any kind of data analysis. In Power BI, this process of bringing tables together is called "merging," and it's the key to transforming separate datasets into a single, cohesive story. This guide will walk you through exactly how to merge queries in Power BI, explaining the different types of joins and providing a clear, step-by-step process.

Why Merging Queries is a Game-Changer

Most businesses don't keep all their useful information in one massive file. Your sales transactions might be in one table, your customer details in another, and product information in a third. Separately, they tell you a little. Together, they create a complete picture.

Imagine you have a list of sales transactions that includes Order IDs and Product IDs, but not the names of the products or the customers' home states. By merging this sales table with your products table (using Product ID) and your customers table (using a Customer ID), you can suddenly answer much more interesting questions:

• What are our top-selling product categories in California?
• Which specific products are most popular with customers in the Northeast?
• Do customers who buy Product A also tend to buy Product B?

Merging allows you to enrich your primary dataset with descriptive context from other tables. It’s how you move from simply reporting what happened (we sold 100 units of Product ID #456) to analyzing why and to whom (we sold 100 units of the 'Pro-Grade Coffee Grinder' primarily to customers in Seattle).

Understanding Join Kinds in Power BI

Before you merge anything, you need to understand the concept of "Join Kinds." This is simply the rule that tells Power BI how to combine your two tables. You’ll select a join kind based on what information you are trying to keep or exclude.

To make this tangible, let's use a simple example with two tables:

1. A Sales table with OrderID, ProductID, and Revenue.
2. A Products table with ProductID, ProductName, and Category.

Our goal is to create a single report that shows the revenue for each sale alongside the actual ProductName and Category. The common column we will use to connect them is ProductID.

Left Outer Join (The Most Common)

A Left Outer join keeps all the rows from your first table (the "left" one) and brings in any matching rows it can find in the second table (the "right" one). This is the default and most frequently used join.

Example: If you select your Sales table first (left) and then the Products table (right), a Left Outer join gives you every single sale from your sales records. For each sale, it looks up the ProductID in the Products table and adds the ProductName and Category. If a sale was recorded for an outdated Product ID that no longer exists in your Products table, that sale would still appear in your merged table, but the ProductName and Category fields would be empty (null).

Right Outer Join

This is the exact opposite of a Left Outer Join. It keeps all the rows from your second ("right") table and only the matching rows from the first ("left") table.

Example: If we did a Right Outer Join with Sales (left) and Products (right), the result would include every product in your catalog. For products that have sold, it would show the associated sales data. For any products that have never been sold, they would still appear on the list, but the OrderID and Revenue fields would be null. This could be useful for quickly identifying which products aren't selling.

Full Outer Join

A Full Outer join keeps all the rows from both tables, matching them up where possible. If there's no match for a row from either side, the columns from the other table will be null.

Example: Using our Sales and Products tables, this join would create a master list of all sales and all products. You would see every sale record and every product record. This type of join is less common for reporting but is excellent for auditing data to find mismatches, like sales recorded for products that don't exist, and products that have never been sold.

Inner Join

An Inner join is the most restrictive. It only keeps rows that have a match in both tables. Think of it as the intersection where both datasets overlap.

Example: An Inner join on our tables would result in a list that only includes sales that have a valid, matching ProductID in the Products table. Any "orphan" sales records with a bad ID would be excluded, and any products that have never sold would also be excluded.

Left Anti Join

This join is used for finding what's in the first table but not in the second. It keeps only the rows from the left table that do not have a match in the right table.

Example: A Left Anti join with Sales (left) and Products (right) would exclusively show you a list of every sale that was recorded with a ProductID that doesn't exist in your Products table. It’s the perfect tool for finding and cleaning up data entry errors.

Right Anti Join

As you can guess, this is the inverse. It keeps only the rows from the right table that do not have a match in the left table.

Example: A Right Anti join with Sales (left) and Products (right) would give you a list of every single product in your catalog that has zero sales records. It answers the question, "What products have we never sold?"

Step-by-Step Guide to Merging Queries in Power BI

Now let's put it into practice. Merging happens in Power BI's Power Query Editor, the engine room for all data transformation.

Step 1: Open the Power Query Editor

From the main Power BI Desktop dashboard, click on the Home tab in the ribbon at the top of the screen. Look for the button that says Transform data and click it. This will launch a new window, the Power Query Editor.

Step 2: Start the Merge Queries Process

Inside the Power Query Editor, you'll see your loaded tables listed in the Queries pane on the left. First, select the table you want to be your primary (left) table. In our case, click on the Sales table.

Now, on the Home tab in the ribbon, look for the Combine section. Here, you'll see a button labeled Merge Queries. Clicking this button merges the data into the table you currently have selected. If you click the small arrow on the button, you'll see an option called Merge Queries as New. This is often a safer choice as it creates a brand new, third table with your combined results, leaving your original two tables untouched.

Step 3: Configure the Merge

A "Merge" dialog box will appear. Your chosen left table, Sales, is already selected at the top.

• In the second dropdown menu, select your right table. In this case, Products.
• Next, you need to tell Power BI which columns to match. Click on the header of the common column in each table — click on ProductID in the Sales table, then click ProductID in the Products table. They will both highlight in yellow. Tip: You can hold down Ctrl to select multiple columns if you have a multi-part key.
• Take a look at the bottom of the dialog box. Power BI will give you a quick check, telling you something like, "The selection matches X of Y rows from the first table." This is an incredibly helpful gut check to see if your data is matching as expected.

Step 4: Choose Your Join Kind

Use the Join Kind dropdown to select the type of merge logic you want to use. To add product information to our sales data and make sure we don't drop any sales, we’ll choose Left Outer (all from first, matching from second). Click OK.

Step 5: Expand the New Column

You’ll be taken back to the Power Query Editor. At the far right of your Sales table, there's a new column. Its header will likely be Products, and every cell in the column simply says "Table."

This is not an error! Power BI has nested the entire matching Products table within each row. To see the columns you want, you need to "expand" this column.

• Click on the expand icon (two arrows pointing in opposite directions) in the column header.
• A menu will pop up, listing all the columns available in the Products table (ProductID, ProductName, Category).
• Select only the columns you want to add to your sales table. We only need ProductName and Category. We should uncheck ProductID because we already have that column in our Sales table.
• Also, uncheck the box at the bottom that says Use original column name as prefix. If you leave this checked, your new columns will be named awkwardly like Products.ProductName. Unchecking it gives you cleaner column names.
• Click OK.

Voila! You will now see two new columns, ProductName and Category, added to the end of your Sales table, with the correct information populated for each sale.

Step 6: Apply and Close

Once you are satisfied with your merged table, look to the top-left corner of the Power Query Editor window and click Close & Apply. This will save all your changes, load your new combined data table into your main Power BI data model, and make it ready to use in dashboards and reports.

Tips for a Smooth Merge

Encountering issues? Here are a few common watch-outs that can cause merges to fail or produce unexpected results.

• Data Types Matter: Ensure the linking columns have the same data type. If ProductID is a "Whole Number" in one table and "Text" in another, Power BI may struggle to find matches. You can easily change data types in the Power Query Editor before merging.
• Clean Your Data First: Pesky extra spaces can ruin a join. Use Power Query's "Trim" function (under the Transform tab) on your key columns to remove leading and trailing whitespace. Similarly, using the "Lowercase" or "Uppercase" functions can help resolve case-sensitivity issues.
• Check for Blanks or Nulls: Blank values in a key column won't match anything. Decide if these blanks are okay or if they represent missing data that needs to be fixed before you attempt to merge.

Final Thoughts

Learning to correctly merge queries in Power BI's Power Query Editor is a non-negotiable skill for serious analysis. It’s what lets you break down data silos and build a comprehensive dataset that tells a complete story. By understanding the different join kinds, you gain precise control over your data, allowing you to ask and answer much more sophisticated questions.

Mastering skills like this is a huge step forward, but you’ll soon find that connecting sources, cleansing data, and building these relationships is still a time-consuming step that happens before the real analysis begins. At Graphed, we handle all that complexity behind the scenes. We connect your marketing and sales data in a few clicks, automatically sorting out the data models and relationships for you. That means you can skip straight to asking questions in plain English — like "compare Facebook Ad spend vs. Shopify revenue by campaign" — and get your answer in a real-time dashboard in seconds, without ever needing to manually configure a single join.