How to Combine Two Data Sets in Power BI

Combining different data sets is one of the first major hurdles you'll face when building reports in Power BI. Thankfully, you don't need to be a data wizard to do it. This tutorial will walk you through the two main ways to combine data in Power BI: merging and appending. We'll show you step-by-step how to use each method and explain when one is a better fit than the other.

Merge vs. Append: What's the Difference?

Before jumping into the step-by-step process, it’s important to understand the fundamental difference between merging and appending queries. While both combine data, they do so in entirely different ways.

Think of it like working with a spreadsheet of your team members:

• Merging is for adding more columns. Imagine you have one spreadsheet with employee names and job titles. You have a second spreadsheet with employee names and their office phone numbers. Merging lets you combine these to create a single master list with three columns: Name, Job Title, and Phone Number. You are enriching your existing data by adding more context. In database terms, this is a "join."
• Appending is for adding more rows. Now imagine your company's sales team is split into 'East Region' and 'West Region,' and you have a separate sales report for each. If both reports have the exact same columns (Date, Sale Amount, Rep Name), you can append them. This means you stack one table on top of the other to create one long, continuous list of all company sales. You are stacking similar data sets together. In database terms, this is a "union."

In short: use merge to make your table wider (more columns), and use append to make it taller (more rows).

How to Merge Queries in Power BI

Let's walk through a common business scenario. Imagine you have a Sales table with transaction data and a separate Products table with details about each product. We want to merge these to analyze sales by product category.

Our Sales table might look like this:

• TransactionID
• ProductID
• UnitsSold
• SaleDate

And our Products table looks like this:

• ProductID
• ProductName
• Category
• Price

The common link between them is the ProductID column. Let's merge them.

Step 1: Get Your Data into Power BI

First, load both tables into Power BI. You can do this from the Home tab by clicking Get Data and choosing your source (like Excel, SQL Server, etc.).

Step 2: Open the Power Query Editor

Once your data is loaded into Power BI, you need to open the Power Query Editor, which is where all the data transformation magic happens. On the Home tab of the main Power BI window, click Transform data. This will open a new window for the Power Query Editor.

Step 3: Choose the Merge Option

In the Power Query Editor, you'll see your queries (your tables) listed on the left pane. Select the Sales query. Now, navigate to the Home tab within Power Query and find the Merge Queries button in the "Combine" section.

You have two choices:

• Merge Queries: This adds the merged data directly into your currently selected table (the Sales table in this case).
• Merge Queries as New: This creates a brand new, combined table and leaves your original tables untouched.

As a best practice, especially when you're starting out, it's safer to use Merge Queries as New. This keeps your original data clean and makes troubleshooting easier.

Step 4: Configure the Merge Window

After clicking "Merge Queries as New", a dialog box will pop up. This is where you tell Power BI how to connect the two tables.

1. Your first table (Sales) will already be selected at the top.
2. In the dropdown menu below it, select your second table (Products).
3. Now, the crucial step: click on the common column header in each table to tell Power BI what to match on. In our case, click on the ProductID column in the Sales table, and then on the ProductID column in the Products table. You'll see them both highlight.

Understanding Join Kinds

The final option in this window is the "Join Kind." This tells Power BI exactly how to handle matches and non-matches between your two tables. While there are a few options, these are the most common:

• Left Outer (default): This keeps every single row from the first (top) table and brings in matching data from the second table. If a sale in your Sales table has a ProductID that doesn't exist in your Products table, that sales row will still appear, but the product fields will be blank (null). This is the most frequently used join type.
• Inner: This only keeps rows that have a match in both tables. If a sale has a ProductID that doesn't exist in the product list, that entire sales record will be excluded from the new combined table. This is useful for cleaning data and focusing only on complete records.

For our example, we'll stick with the default Left Outer join and click OK.

Step 5: Expand Your New Column

You’ll now have a new table (likely called Merge1). Look to the far right, and you'll see a column with the name of the second table you merged (Products). Each cell in this column says "[Table]", not the actual data you want.

To pull in the specific columns you need, click the expand icon in that column's header (it looks like two arrows pointing in opposite directions). A dialog box will appear, listing all the columns from the Products table.

Select the columns you want to add to your master table - for example, ProductName and Category. We don't need ProductID again, since it's already in our Sales table. Also, uncheck the box that says "Use original column name as prefix." This gives you cleaner column names like "Category" instead of "Products.Category". Click OK.

Step 6: Close & Apply

Voilà! Your tables are now merged. You have one wide table with your sales data enriched with product details. To make this new table available in your Power BI reports, click Close & Apply in the top-left corner of the Power Query Editor.

How to Append Queries in Power BI

Now, let’s tackle appending. Remember, this is for stacking tables with identical columns. Let's say you have monthly sales reports from two different regions: North_Region_Sales and South_Region_Sales.

Both tables have the same structure:

• OrderID
• Product
• Revenue
• SaleDate

Our goal is to create a single Total_Sales table for company-wide reporting.

Step 1: Load Data and Open Power Query

Just as before, get both tables into Power BI and open the Power Query Editor by clicking Transform data.

Step 2: Start the Append Process

With any of your queries selected (it doesn't matter which one), navigate to the Home tab and click the Append Queries button.

Like with merge, you have two options: "Append Queries" to add to an existing table or "Append Queries as New". We'll once again choose Append Queries as New to create a combined table without modifying the originals.

Step 3: Choose Tables to Append

A new window will appear.

• If you're combining just two tables, leave "Two tables" selected.
• Choose North_Region_Sales as your primary table and South_Region_Sales as the table to append. The order doesn't matter here.
• If you have three or more tables to combine (e.g., quarterly reports), you can choose "Three or more tables" and multi-select the tables from the list.

Click OK.

Step 4: Check Your Results

Power Query will instantly create a new table with all the rows from North_Region_Sales stacked on top of all the rows from South_Region_Sales.

Quick Tip: Appending works by matching column headers. If one table has a column named SaleDate and the other has Transaction_Date, Power BI will create two separate date columns in your appended table, with lots of blanks. To avoid this, make sure your column names are perfectly consistent across all tables before you append them. You can easily rename columns in the Power Query Editor by double-clicking the header.

Step 5: Close & Apply

With your new, taller table ready, click Close & Apply on the Home tab. Your newly created Total_Sales table is now in your data model, ready for you to build visualizations.

Bonus Tips for Combining Data

• Data Types are Important: Ensure that common columns used for merging (like ProductID) have the same data type (e.g., Text, Whole Number) in both tables to avoid errors.
• Be Mindful of Performance: Merging very large tables can slow down your report's refresh time. Whenever possible, try to join data closer to the source (e.g., in the SQL database itself) before it ever gets to Power BI.
• Document Your Steps: Power Query records every step you take in the "Applied Steps" pane on the right. You can rename these steps to remember what you did, making it easier to edit your query later.

Final Thoughts

Mastering merge and append operations in Power Query unlocks the true potential of Power BI, allowing you to seamlessly integrate various data sources for comprehensive analysis. Merging adds width to your data by adding new columns from related tables, while appending adds height by stacking complementary rows.

Learning these steps is fundamental for any serious analysis, but when it's your job to analyze data from dozens of disconnected platforms like Google Analytics, Facebook Ads, Shopify, and Salesforce, this process can become a tedious daily routine. Instead of manually exporting, cleaning, and merging data, we built Graphed to do the heavy lifting for you. We connect to your marketing and sales tools, consolidating all your data in one place so you can instantly build reports and dashboards with simple natural language - no Power Query required.