How to Merge Queries in Power BI

Stitching together data from different sources is a daily reality in data analysis, and Power BI's Merge Queries feature is your best tool for the job. It lets you combine tables based on a shared piece of information, much like using VLOOKUP in Excel but on a more powerful and flexible scale. This guide will walk you through exactly how to combine tables using Merge Queries, explaining the different join types with simple examples so you can confidently shape and enrich your data.

What are Merge Queries in Power BI?

Merging queries in Power BI means combining two different tables into one by matching rows based on a common column. Think about it this way: you have one table with your sales data (Order ID, Customer ID, Product Sold) and another with customer details (Customer ID, Name, City, State). By themselves, each table only tells part of the story. You can't see which states generate the most sales, or who your top customers are by name.

Merging allows you to connect these two tables using the common "Customer ID" column. The end result is a single, wider table that includes Order ID, Customer ID, Product Sold, Customer Name, City, and State. You've enriched your sales data with customer demographic information, unlocking deeper analysis possibilities.

All of this work happens in the Power Query Editor, which is the data transformation engine inside Power BI. It's the workshop where you clean, shape, and prepare your data before it gets to the report-building stage.

Merge vs. Append: What's the Difference?

Before we go further, it's important to clear up a common point of confusion: the difference between merging and appending queries. They sound similar but do opposite things.

• Merge Queries combines tables horizontally by adding new columns. It makes your table wider. You use this when you have different information about the same thing (like orders and customer details).
• Append Queries combines tables vertically by adding new rows. It makes your table taller. You use this when you have the same type of information split across multiple tables (like monthly sales reports for January, February, and March).

Imagine Tables A and B. Merging is like placing them side-by-side to add more descriptive columns. Appending is like stacking Table B directly underneath Table A to create a longer list.

A Step-by-Step Guide to Merging Queries

Let's walk through a practical example. Say we have a Sales table and a Products table. We want to merge them to see the product category for each sale.

Our Tables:

• Sales Table: Contains OrderID, ProductKey, SaleAmount
• Products Table: Contains ProductKey, ProductName, Category

The common column here is ProductKey. Let's get started.

Step 1: Open the Power Query Editor

First, you need to be in the Power Query Editor. In the main Power BI Desktop window, go to the Home tab and click on Transform data. This will open a new window where all the data magic happens.

Step 2: Select Your Starting Table

In the left-hand Queries pane, select your main or "left" table. In our case, this is the Sales table. You want to add information to this table.

Step 3: Launch the Merge Queries Tool

With the Sales query selected, navigate to the Home tab within the Power Query Editor. In the "Combine" group, click a dropdown on Merge Queries. You will see two options:

• Merge Queries: This adds the merged data as new columns to your currently selected table. It’s a direct modification.
• Merge Queries as New: This creates a brand-new, third table that contains the result of the merge, leaving your original tables untouched.

Pro Tip: When you're learning, it's often safer to use Merge Queries as New. This way, if you make a mistake, your original tables are unchanged, and you can just delete the new merged table and try again.

Step 4: Configure the Merge Window

A new "Merge" window will pop up. This is where you configure the operation.

1. Your first table (Sales) will already be selected at the top.
2. In the dropdown menu below it, select the second table you want to pull data from. We'll pick the Products table.
3. Next, you need to tell Power BI which columns to match. Click on the ProductKey column in the Sales table, then click on the ProductKey column in the Products table. Both will be highlighted.
4. At the bottom of the window, Power BI gives you a helpful message telling you how many rows matched. This is a great quick check for data quality.

Now for the most important part: the Join Kind.

Understanding the Different Join Types

The "Join Kind" determines how Power BI combines the rows from your two tables. There are six options, each serving a different analytical purpose. We'll use our Sales and Products example to clarify.

Left Outer (the default)

This includes all rows from the first (left) table and only the rows that match from the second (right) table. In our example, every single sale would be kept. If a sale happened for a ProductKey that doesn't exist in our Products table for some reason, the product name and category fields for that row would be blank (null).

• Use Case: This is the most common join. You want to enrich your primary table without losing any of its original records.

Right Outer

This is the reverse. It keeps all rows from the second (right) table and only matching rows from the first (left). All products from our Products table would be listed. If a product has never been sold, it will still appear in the list, but its sales-related fields (OrderID, SaleAmount) will be null.

• Use Case: Useful for finding items in a dimension table that have no corresponding transactions, like products that have never been sold.

Full Outer

This brings in all rows from both tables. If there's a match, the data is combined. If a sale has no matching product or a product has no matching sale, they are still included in the final table with nulls in the columns from the opposing table.

• Use Case: When you need a complete picture of everything from both tables, regardless of whether a match exists.

Inner

This only keeps rows that have a match in both tables. If a sale has a bad ProductKey that isn't in the Products table, that sale will be excluded. Likewise, if a product has never been sold, it will also be excluded.

• Use Case: Perfect for creating a clean, validated dataset where you only want to analyze records that are fully connected.

Left Anti

This is a filter. It returns only the rows from the first (left) table that have no match in the second (right) table. It would show us any sales where the ProductKey did not match any entry in our Products table.

• Use Case: An excellent data quality check for finding "orphaned" records or transactions with invalid IDs.

Right Anti

The opposite of Left Anti. It returns only the rows from the second (right) table with no match in the first (left) table. This would give us a list of all products that have never been sold.

• Use Case: Identifying unused or inactive items, like customers who haven't made a purchase or products sitting on the shelf.

For our goal, we want to add product information to all our sales, so we'll pick the standard Left Outer join and click OK.

Step 5: Expand The New Column

After you click OK, you'll be taken back to the Power Query Editor. You'll see a new column has been added to your table, likely called Products (or whatever the name of your second table was). Instead of data, every row in this column just says "Table".

This is not an error! Power BI has packed all the matching information from the Products table into this structured column. To see the data, you need to expand it.

1. Click on the expand icon in the column header (it looks like two arrows pointing in opposite directions).
2. A dropdown menu will appear showing all the columns from the Products table. Since we already have ProductKey in our Sales table, we can uncheck that. Let’s select ProductName and Category.
3. Make sure to uncheck the box that says "Use original column name as prefix." This prevents your new columns from being named Products.ProductName and instead gives them cleaner names like ProductName.
4. Click OK.

Voila! Two new columns, ProductName and Category, have been added to your sales table, with the correct information matched for every sale. Your merge is officially complete. You can now click Close & Apply on the Home tab to load your new, cleaned-up data into Power BI for analysis.

Final Thoughts

Merging queries is a foundational skill in Power BI that turns separate, siloed datasets into a unified and powerfully interconnected data model. By understanding how to select your tables, a common key, and the right join kind for your goal, you have already moved past simple visualizations and into creating truly insightful reports. You can now build a comprehensive view of how all the pieces of your business connect.

While mastering a powerful BI tool is incredibly valuable, sometimes the biggest hurdle is just getting all your marketing and sales data in one place to get a quick answer. As a team that has lived in reporting spreadsheets, we built Graphed to radically simplify that process. We let you connect sources like Shopify, Facebook Ads, Google Analytics, and Salesforce instantly. Then, instead of jumping through menus to merge data, you just ask questions in plain English like, "show me a dashboard comparing Facebook Ads spend vs revenue for the last 30 days." We build the dashboards for you in real time by connecting the data behind the scenes, letting you focus on the insights instead of the setup.