How to Merge Data in Power BI

Bringing different datasets together in Power BI is essential for creating comprehensive reports, but knowing where to start can feel tricky. This guide will walk you through how to merge data in Power BI so you can combine, clean, and enrich your data for better, more insightful dashboards and reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Merge vs. Append: What's the Difference?

Before jumping into the steps, it's important to understand the difference between merging and appending queries in Power BI, as they solve different problems.

• Merging Queries: This is like using VLOOKUP or XLOOKUP in Excel. You combine tables horizontally by adding new columns from one table to another based on a matching column (a key). For example, you could merge a sales table with a product details table using ProductID to add columns like ProductName and ProductCategory to your sales data. You're making your table wider.
• Appending Queries: This is like stacking data from similar tables on top of each other. You combine tables vertically. For example, if you have separate tables for sales data from January, February, and March (and they all have the same columns), you could append them to create a single, master table with Q1 sales data. You're making your table longer.

This article focuses exclusively on merging queries - the process of enriching one dataset with columns from another.

Why Merge Data in Power BI?

Merging is a foundational skill in data preparation because raw data rarely exists in a single, perfectly formatted table. Usually, you have information scattered across different sources. Merging allows you to stitch it all together coherently.

Here are a few common scenarios where you'll need to merge data:

• Enriching Transactional Data: Your Shopify export might have CustomerID but not the customer's name or contact information. You can merge your sales data with a customer data table from your CRM to add these crucial details.
• Connecting Marketing and Sales Funnels: You want to know which marketing campaigns drove the most sales. You can merge your ad platform data (like from Facebook Ads or Google Ads) with your sales data (from your e-commerce store or CRM) using a common field like a UTM parameter or date.
• Lookup Static Information: Imagine you have a lookup table that maps employee IDs to their respective departments and managers. You can merge this with a performance results table to analyze results by department.

How to Merge Data in Power BI: A Step-by-Step Guide

Let's use a practical example. Say we have two tables:

1. A 'Sales' table with transactional information: OrderID, SaleDate, ProductID, and UnitsSold.
2. A 'Products' table with product details: ProductID, ProductName, and ProductCategory.

Our goal is to merge these tables to analyze sales performance by ProductName and ProductCategory, not just ProductID.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Open the Power Query Editor

Merging takes place in the Power Query Editor, which is Power BI's data transformation engine. First, ensure your data is loaded into Power BI. Then, from the main Power BI Desktop window, go to the Home tab and click on the Transform data button. This will launch the Power Query Editor.

Step 2: Start the Merge Query Process

In the Power Query Editor, you'll see your loaded queries (your tables) listed in the Queries pane on the left. First, select the primary table you want to add columns to. In our case, this is the 'Sales' table.

Next, navigate to the Home tab within the Power Query Editor. In the 'Combine' group, click the Merge Queries dropdown. You have two options:

• Merge Queries: This modifies your current table (Sales) by adding the new columns directly to it.
• Merge Queries as New: This creates a brand new, third table that contains the merged data, leaving your original 'Sales' and 'Products' tables untouched.

For most uses, modifying the current table is fine. Let's select Merge Queries.

Step 3: Configure the Merge Window

A new 'Merge' window will pop up. This is where you tell Power BI how to connect your two tables.

1. The top section shows a preview of your primary table ('Sales').
2. From the dropdown menu below it, select the second table you want to pull data from. In our case, this is the 'Products' table.
3. Select the Matching Columns: This is the most crucial part. Click on the column header in each table that contains the matching values. We'll click on the ProductID column in the 'Sales' table, and then click on the ProductID column in the 'Products' table. Power Query will highlight them and show you an estimate of how many rows match.

Step 4: Choose the Right Join Kind

The 'Join Kind' determines how the tables are merged based on the matching keys. Power BI offers several types of joins, but here are the three most common ones you'll use:

• Left Outer (the default): This is the most frequently used join. It keeps all rows from the first (top) table and only brings in the matching rows from the second (bottom) table. If a row in the first table doesn't have a match in the second, the new columns will just show 'null'. This is perfect for our example because we want to keep all our sales records and just add product info where available.
• Inner Join: This keeps only the rows that have a match in both tables. If a ProductID in the 'Sales' table doesn't exist in the 'Products' table, that entire sales record will be excluded from the final table. This is useful for cleaning data and focusing only on complete records.
• Full Outer Join: This keeps all rows from both tables, whether they have a match or not. It's essentially the union of both tables.

For our goal, Left Outer is what we need. Select it and click OK.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Expand the New Column

After you click OK, you'll be taken back to the Power Query Editor. Your 'Sales' table now has a new column at the end, likely named after your second table ('Products'). However, the cells in this column will just say "Table".

This is because Power BI has bundled up all the potential columns from your 'Products' table into this one column. To see the data, you need to expand it. Click the small expand icon with two arrows in the column header.

Step 6: Select the Columns you want to add

A dropdown will appear showing all the columns from the 'Products' table. Check the boxes for the columns you want to add to your 'Sales' table. We want ProductName and ProductCategory.

One important tip: uncheck the box that says "Use original column name as prefix." If you leave it checked, your new columns will be named awkwardly (e.g., Products.ProductName). Unchecking it keeps the column names clean (e.g., ProductName).

Click OK. You'll now see your two new columns added to the end of your 'Sales' table. Each sales row is now enriched with its corresponding product name and category!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 7: Close & Apply Your Changes

Once you are happy with your merged table, click the Close & Apply button in the top-left corner of the Power Query Editor. This saves your changes and loads the new, merged table into your Power BI data model, making it ready to use in your reports and dashboards.

Best Practices and Common Problems

Merging should be straightforward, but a few common issues can trip you up. Here are some best practices to follow:

• Check Your Data Types: The matching columns in both tables must have the same data type. You can't merge a 'Text' ProductID with a 'Whole Number' ProductID. You can change data types by clicking the icon to the left of the column header in Power Query.
• Clean Your Key Columns: Trailing spaces ("US-123 ") or different capitalization ("product-a" vs "Product-A") can prevent a merge from working correctly. Use the 'Transform' tab in Power Query to 'Trim' and 'Clean' your data or change 'Capitalization' before merging.
• Be Mindful of Performance: Merging very large datasets can impact the performance of your Power BI report. Before you merge, use the 'Choose Columns' and 'Filter Rows' features to only bring in the data you actually need for your analysis. The leaner your tables, the faster your merges will be.
• Validate Your Output: After merging, quickly sort or filter your data to look for null values in the newly added columns. This can help you spot any issues with your keys not matching up as expected.

Final Thoughts

Being able to merge data in Power BI is a fundamental-yet-powerful skill that lets you move beyond simple, one-dimensional datasets. By connecting different tables based on common columns in Power Query, you can build a rich, unified model that tells a much more complete story about your business performance.

The manual steps involved in merging data across different sources, like in Power BI, can be powerful but also time-consuming. We built Graphed to automate this entire process. Instead of managing joins, data types, and transformation steps yourself, our platform allows you to connect all your sources in just a few clicks. Then, you can simply describe the combined dashboard you want in plain English, and our AI builds it in seconds, helping you get from raw data to actionable insights without ever leaving our tool.