How to Merge Tables in Power BI

Bringing data together from different sources is a superpower for any analyst. In Power BI, this often means merging tables to create a unified view for your reports. While it sounds complicated, merging is a straightforward process inside the powerful Power Query Editor. This tutorial will walk you through exactly how to merge tables in Power BI, explaining the different join types so you can combine your data with confidence.

What’s the Difference Between Merging and Appending in Power BI?

Before we jump into the "how-to," it’s important to understand a key distinction in Power BI: merging vs. appending.

• Appending is like stacking tables on top of each other. You do this when you have tables with the same columns and you want to add more rows. For example, you might append your January sales data with your February sales data to get a single table with sales for both months. It increases the number of rows.
• Merging is like using a VLOOKUP or INDEX(MATCH) in Excel, but far more powerful. You combine tables side-by-side based on a shared column. For example, you could merge a sales transaction table with a product details table using a common 'Product ID' to bring the product name and price into your sales data. It increases the number of columns.

In this guide, we are focusing exclusively on merging, which adds new columns of related information to your primary table.

Your Gateway to Merging: The Power Query Editor

All data transformation magic in Power BI, including merging tables, happens in the Power Query Editor. This is a separate window from the main Power BI Desktop interface where you build your visuals. Think of it as your workshop for getting your data clean and ready for analysis.

To open Power Query, go to the Home tab in Power BI Desktop and click on the Transform data button. This will launch the Power Query Editor window with all your loaded data queries listed on the left.

A Step-by-Step Guide to Merging Tables

The best way to learn is by doing. Let’s walk through a common business scenario. Imagine we have two tables:

1. A Sales table with TransactionID, ProductID, Quantity, and Date.
2. A Products table with ProductID, ProductName, and ProductCategory.

Our goal is to analyze sales by ProductCategory, but that information only exists in the Products table. To do this, we need to merge the two tables using the common ProductID column.

Step 1: Start the Merge Operation

In the Power Query Editor, first, select the main table you want to add columns to. In our case, that’s the Sales table. This will be our "left" table.

Next, on the Home tab of the Power Query ribbon, find the Combine section and click Merge Queries. You will see two options:

• Merge Queries: This modifies your currently selected (Sales) table by adding the new columns to it.
• Merge Queries as New: This leaves your original Sales and Products tables untouched and creates a new, third table containing the merged result.

For beginners or if you want to keep your original tables clean, it’s often safer to choose Merge Queries as New. This way, you can always go back to your original source data if you make a mistake.

Step 2: Configure the Merge Window

After clicking, the Merge window will appear. This is where you tell Power BI how to connect your tables.

1. The top dropdown will already show your "left" table (Sales).
2. In the second dropdown, select the other table you want to get data from — our "right" table (Products).
3. Now for the most important step: select the matching column in each table. Click the ProductID column header in the Sales table, and then click the ProductID column header in the Products table. Power BI will highlight them and show a checkmark to let you know it has established the connecting key.

At the bottom of this window, you’ll see the Join Kind dropdown. This is where you define the logic for your merge. Let's explore what these options mean.

The 6 Join Types, Explained Simply

The "Join Kind" determines which rows are kept in the final merged table. Understanding these is crucial for getting the results you need. Power BI offers six types of joins.

1. Left Outer Join (the most common)

This is the default setting and the one you'll use most often. A Left Outer Join keeps all rows from your first (left) table and brings in the matching values from the second (right) table.

• In our example: Every single sale from the Sales table will be included in the new table. If a sale was recorded for a ProductID that doesn't exist in the Products table (perhaps a data entry error), it will still appear, but the columns for ProductName and ProductCategory will be empty (null).

2. Right Outer Join

This is the reverse of a Left Outer Join. It keeps all rows from your second (right) table and brings in matching values from the first (left) table.

• In our example: Every product from the Products table will be included. If a product has never been sold, it will still appear on the list, but the columns for TransactionID, Quantity, and Date will be empty (null). This could be useful for identifying unsold inventory.

3. Full Outer Join

This join keeps all rows from both tables, whether they have a match or not. It's essentially the output of a Left Outer and Right Outer join combined.

• In our example: You would get a list showing every sale and every product. Unsold products would have nulls in the sales columns, and sales of invalid products would have nulls in the product columns. It's great for spotting discrepancies between two datasets.

4. Inner Join

An Inner Join keeps only the rows that have a match in both tables. Think of it as the "perfect matches only" option.

• In our example: The final table will only contain sales for products that successfully found a match in the Products table. Any sales transactions with a bad ProductID (one not listed in the Products table) would be completely excluded from the result. This is a fantastic way to clean your data and work with only a validated set of transactions.

5. Left Anti Join

This is a diagnostic join. It returns only the rows from the first (left) table that do not have a match in the second (right) table.

• In our example: This would give you a list of all sales transactions where a matching ProductID could not be found in the Products table. This is an extremely useful technique for finding and fixing data entry errors.

6. Right Anti Join

The reverse of the Left Anti Join. It returns only the rows from the second (right) table that do not have a match in the first (left) table.

• In our example: This would create a list of all products from your Products table that have never appeared in any transaction in the Sales table. Instant insight into which products have zero sales!

For our goal, a Left Outer Join makes the most sense. We want to keep all our sales records and just add the product details. Select this option and click OK.

Step 3: Expand the New Column to Get Your Data

After you click OK, you'll be taken back to the Power Query Editor. You won’t see your new columns right away. Instead, you'll see a single new column (usually named after your "right" table, so "Products" in our case) with the word "Table" in each cell.

This is normal! Power BI has nested the entire contents of the right table into this column. To select the specific columns you want, click the expand icon (the two arrows pointing outward) in that column's header.

A dropdown will appear showing all the columns from the Products table. You can now:

• Select the columns you want to add. We need ProductName and ProductCategory. We can uncheck ProductID because we already have it in our sales table.
• Uncheck the box that says "Use original column name as prefix." If you leave it checked, your new columns will be named Products.ProductName and Products.ProductCategory. Unchecking it gives you cleaner names: ProductName and ProductCategory.

Click OK. Voila! You now have your product name and category columns sitting right next to your sales data, ready for analysis.

Step 4: Close & Apply

Your data transformation is complete. The final step is to load this new, merged query into your Power BI data model. Click the Close & Apply button in the top-left corner of the Power Query Editor.

Now, back in the main Power BI window, you’ll see your new merged table in the Fields pane on the right. You can immediately start building visuals using fields from both of the original tables, like a bar chart showing Sales Quantity by ProductCategory.

Final Thoughts

Merging tables is a fundamental skill that unlocks deeper levels of data analysis in Power BI. By using Power Query and understanding the different join types, you can move beyond simple, one-dimensional datasets and create rich, connected data models that tell a complete story.

While tools like Power BI are incredibly powerful, they still involve dozens of manual steps like configuring joins, cleaning data prefixes, and setting up data models. At Graphed, we automate this entire process. You simply connect your data sources — like Google Analytics, Shopify, or Salesforce — and then ask questions in plain English. Our AI handles the work of joining and structuring the data in the background, creating live, interactive dashboards in seconds, not hours.