How to Join Datasets in Power BI

Bringing all your data into one place is the first step toward building a powerful report. In Power BI, your data often lives in separate tables - sales in one, customer details in another, and product info in a third. This article will show you how to join those datasets using Power BI’s two primary methods: appending and merging queries.

Why Join Datasets in Power BI?

Rarely does a single table contain all the information you need. You might have transaction data from Shopify, customer information from Salesforce, and ad campaign performance from Google Ads. To get a complete picture of your business, you need to connect these dots. Joining datasets allows you to create a unified view, which is the foundation for creating insightful and comprehensive reports.

For example, you could:

• Combine a list of sales transactions with a customer details table to analyze purchasing habits by city or demographic.
• Connect product inventory data with sales data to identify which out-of-stock items have the highest demand.
• Link website traffic data from Google Analytics to your CRM data to see which marketing channels are bringing in the most valuable leads.

By connecting separate tables, you transform isolated data points into a connected story, enabling you to uncover relationships you simply couldn’t see before.

Understanding the Core Concepts: Merge vs. Append

Before you start clicking buttons, it's important to understand the two main ways Power BI combines data. Choosing the wrong one is a common source of error for beginners.

Merge Queries: Adding Columns

Think of merging as expanding a table horizontally. When you merge, you're taking two separate tables that share a common column (like a CustomerID or ProductSKU) and adding columns from one table to the other. It's essentially the Power BI equivalent of a VLOOKUP in Excel or a JOIN in SQL.

For example, if you have a Sales table with a ProductID and a Products table that lists product names, categories, and prices, you can merge them. This allows you to add the product name and category to your Sales table so you can analyze sales figures by category.

Append Queries: Adding Rows

Think of appending as expanding a table vertically — like stacking books. When you append, you're taking two or more tables that have the same column structure and combining their rows into one single, larger table.

A classic example is combining monthly or yearly data. If you have a table for JanuarySales, another for FebruarySales, and a third for MarchSales, you can append them to create a single Q1_Sales table. This is perfect for analyzing trends over longer time periods without having to look at dozens of separate tables.

Step-by-Step Guide: How to Merge Queries in Power BI

Merging is one of the most common data preparation tasks. Let’s walk through the exact steps to merge two tables in Power BI’s Power Query Editor. For our example, let’s say we want to merge a Sales table (containing Order ID, ProductID, and Quantity) with a Products table (containing ProductID and ProductName).

Step 1: Open the Power Query Editor

From the main Power BI Desktop window, go to the Home tab and click on Transform Data. This will launch the Power Query Editor, which is where all data transformation magic happens.

Step 2: Select Your Primary Table and Start the Merge

In the Power Query Editor’s left-hand pane (Queries), select the primary table you want to add columns to. In our case, this is the Sales table. With the table selected, click on the Home tab in the ribbon and find the Merge Queries button. Clicking the dropdown arrow gives you two choices:

• Merge Queries: Modifies your current table by adding the new columns to it.
• Merge Queries as New: Keeps your original tables untouched and creates an entirely new table as the result of the merge.

For beginners, it's often safer to use Merge Queries as New so you don't accidentally alter your original sources. Let's choose that option.

Step 3: Configure the Merge Dialog Box

A dialog box will appear. You've already selected your first (top) table. Now, select your second table (the Products table) from the dropdown menu.

Step 4: Select the Matching Columns

Now, you need to tell Power BI how these two tables are related. Click on the column header of the common column in each table to select it. In our example, you would click on the ProductID column in the Sales table, and then the ProductID column in the Products table. Power BI will show you a small note at the bottom indicating how many rows match.

Step 5: Choose Your Join Kind

The Join Kind determines how the tables are merged. There are several options, but these are the most common:

• Left Outer (the default): Keeps all rows from the first (left) table and brings in matching rows from the second (right) table. If there’s no match for a row from the left table, the new columns will be null. This is usually what you want.
• Inner: Only keeps rows that have a match in both tables. Use this if you only want to analyze data that exists in both datasets.
• Full Outer: Keeps all rows from both tables, regardless of whether they have a match. This is useful for finding discrepancies between two lists.

For our example, a Left Outer join is perfect. Click OK.

Step 6: Expand the New Column

You’ll now see a new column has been added to your table, with a header like [Table]. This column contains all the data from the Products table that can be joined to your Sales data. To select which columns you want to add, click the expand icon (two opposing arrows) in the column header.

A dropdown will appear. Uncheck any columns you don't need - in our case, we probably don't need ProductID again since we already have it. Select ProductName and click OK. You can also uncheck "Use original column name as prefix" to keep your column names clean (ProductName instead of Products.ProductName).

Step 7: Close & Apply

You now have a new, merged table with the ProductName added to your sales data. To save your changes and load the data into your Power BI model, go to the Home tab and click Close & Apply.

Step-by-Step Guide: How to Append Queries in Power BI

Appending is a bit more straightforward. Let’s imagine we have two tables, Sales_2023 and Sales_2024, and we want to combine them into one master sales table.

Step 1: Open the Power Query Editor

Just like with merging, your journey starts in the Power Query Editor. Go to the Home tab and click on Transform Data.

Step 2: Start the Append Process

Go to the Home tab and find the Append Queries button. The same two options will appear:

• Append Queries: Adds the rows from the second table to your currently selected table.
• Append Queries as New: Combines your tables into a brand new query, leaving the original sources untouched.

Again, let’s choose Append Queries as New to create a new AllSales table.

Step 3: Select the Tables to Append

The Append dialog box will pop up. You can choose between combining Two tables or Three or more tables. In our case, we select Two tables, choose Sales_2023 as the Primary table, and Sales_2024 as the table to append.

If you have more than two tables (for example, monthly files), you would select Three or more tables and move all the tables you want to stack from the "Available tables" column to the "Tables to append" column on the right. Click OK.

Step 4: Review and Apply

That's it! A new table named Append1 (you can rename it) will appear in the Queries pane, containing all rows from both Sales_2023 and Sales_2024.

Quick Tip: What happens if the columns don't match? If a column exists in one table but not the other, Power Query will still append the data. It will create the column in the final table and simply fill the missing rows with null values. This flexibility is great, but be sure to check for unexpected nulls.

Once you are happy, click Close & Apply.

Best Practices for Joining Data

Learning the steps is one thing, but applying them efficiently is what will make you a pro. Here are a few tips to keep in mind:

• Check Your Data Types: For merges to work correctly, the common columns must have the same data type. You can't join a text-based UserID to a number-based UserID. Check and change data types in Power Query before you merge.
• Remove Unnecessary Columns First: Your queries will run faster if you remove columns you don't need before you merge or append. This reduces the amount of data Power BI has to process.
• Understand Your Join Kind: The most common error in merging is picking the wrong join kind and either including too much data (with lots of nulls) or filtering out important data without realizing it. Always double-check your join selection.
• Document Your Steps: Power Query automatically documents every step you take in the "Applied Steps" pane. Get in the habit of renaming these steps to something logical (e.g., "Merged with Products Table") so you or a teammate can easily understand your workflow later.

Final Thoughts

Learning how to properly merge and append data is a non-negotiable skill for anyone serious about using Power BI. These functions transform your collection of disparate tables and files into a cohesive, structured dataset that’s ready for powerful analysis and visualization.

While tools like Power BI are incredibly powerful, we know that getting started and connecting all your data sources is still a huge point of friction for many teams. That's why we created Graphed. We simplify the entire process by connecting directly to platforms like Salesforce, Shopify, and Google Analytics. You can skip the manual setup and use simple, natural language to ask questions like, "Show me my top 10 products by total revenue in the last 90 days," and Graphed instantly builds the report for you, joins and all.