What is Merge and Append in Power BI?

Bringing data together from different sources is a fundamental skill in Power BI, but knowing when to use "Merge" versus "Append" can be tricky. These two functions are the bedrock of creating comprehensive reports, allowing you to combine scattershot data into a single, unified view. This guide will walk you through what Merge and Append are, when to use each, and how to perform both operations step-by-step.

First, Why Combine Data at All?

In the real world, your data is rarely stored in one perfect, clean location. You might have your sales data in an Excel file, customer information in a CRM like Salesforce, and website traffic data in Google Analytics. To get meaningful insights - like which marketing campaigns led to the most sales from high-value customers - you need to bring this data together.

This is where Power BI’s Power Query Editor shines. It provides two primary methods for combining data sets: Appending and Merging. They might sound similar, but they solve entirely different problems.

What is Appending Data? Your Guide to Stacking Tables

Think of appending as stacking your data tables on top of each other. When you append, you are adding more rows to an existing table. The goal is to create one longer, master table from multiple tables that share the exact same column structure.

Imagine you have three separate spreadsheets for quarterly sales: one for Q1, one for Q2, and one for Q3. Each file has the same columns: Date, ProductID, Region, and SalesAmount. To analyze the entire year's performance so far, you would append these three tables into one single table. You aren't adding new types of information (columns), you're just adding more of the same information (rows).

When to Use Append Queries

Appending is the right choice when:

• You have data in multiple files or tables that need to be combined into one.
• The files are separated by time (e.g., monthly sales reports, weekly activity logs).
• The files are separated by category but share a common structure (e.g., sales data for your North, South, and West regions).
• The column headers and data types in each table are identical or at least very similar.

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

Let's walk through a practical example. Say we have two tables: Sales_H1 (for the first half of the year) and Sales_H2 (for the second half). Both tables have the same columns: OrderID, Product, and Revenue.

1. Open Power Query Editor: First, load your tables into Power BI. Then, from the Power BI Desktop main window, click on the Transform data button on the Home ribbon. This will open the Power Query Editor.
2. Locate Append Queries: In the Power Query Editor, make sure you're on the Home ribbon. In the "Combine" group, you will see a button labeled Append Queries.
3. Choose Your Append Option: Clicking the dropdown gives you two choices:
4. Configure the Append Dialog: A pop-up window will appear. You can choose to combine "Two tables" or "Three or more tables." Since we only have Sales_H1 and Sales_H2, we’ll stick with two.
5. Review the Result: Power Query will instantly create a new query (likely named Append1). This new table contains all the rows from Sales_H1 followed by all the rows from Sales_H2. You can rename this new query to something more descriptive, like Total_Sales_2024.

Pro Tip: Power Query matches columns based on their headers. If one table has a column named Revenue and the other has Revenue ($), Power Query will treat them as two separate columns, leading to null values. Ensure your column names are consistent before appending!

What is Merging Data? Your Guide to Joining Tables

If appending is about stacking data to add more rows, merging is about joining data tables side-by-side to add more columns. It allows you to enrich one table with information from another, based on a matching or related column. For anyone familiar with Excel, a Merge is like a super-powered VLOOKUP or INDEX(MATCH).

Imagine you have a Sales table that contains SaleID, CustomerID, ProductID, and Quantity. And a separate Products table that contains ProductID, ProductName, Category, and Price. Your Sales table tells you what was sold, but not the name or category of the product. To get that information, you would merge the Sales table with the Products table using the common ProductID column. The result would be a wider, enriched Sales table that now includes ProductName and Category for each sale.

When to Use Merge Queries

Merging is your go-to function when:

• You need to add descriptive columns from a "lookup" or "dimension" table to a "fact" table (e.g., adding Product details to a Sales table).
• You want to combine data from two different systems that share a common identifier (e.g., joining website session data with CRM data using a UserID or email).
• You need to perform a lookup to bring new attributes or metrics into your main dataset.

Understanding Join Kinds (The Easy Way)

When you merge, Power BI will ask you to select a "Join Kind." This just tells it how to handle matching and non-matching rows between the two tables. Don't be intimidated by the options, you'll use one type most of the time.

• Left Outer (Most common): This keeps all the rows from your first (left) table and brings in any matching rows it finds in the second (right) table. If a row in your left table has no match in the right table, the new columns will just be null. This is what you'll use for 90% of your VLOOKUP-style merges.
• Inner: This keeps only the rows that have a match in both tables. Any non-matching rows from either table are discarded.
• Right Outer: The opposite of a left join. It keeps all rows from the second (right) table and only the matching rows from the first (left) table.
• Full Outer: Keeps all rows from both tables, whether they have a match or not.

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

Let's continue with our example. We have our Total_Sales_2024 table from the append step, and we also have a Products lookup table with ProductID and ProductName.

1. Open Power Query Editor: Make sure both your Total_Sales_2024 and Products tables are loaded and you have the Power Query Editor open.
2. Locate Merge Queries: Select the primary table you want to add columns to - in this case, Total_Sales_2024. On the Home ribbon, click Merge Queries. Just like with Append, choose Merge Queries as New to create a new, combined table without altering the originals.
3. Configure the Merge Dialog:
4. Expand the New Column: A new query will be created. You’ll see all your original sales columns, plus a new column named after the merged table (Products). Each row in this new column simply says Table. Don't panic! This is correct. This structured column holds all the matching information from the Products table for that row.
5. Review the Final Result: Voila! Your main table now has a ProductName column, giving you a much richer dataset for building your reports and visualizations. You’ve successfully merged your data.

Merge vs. Append: The Simple Cheatsheet

Still not totally sure which to use? Here's a simple way to decide.

Use Append When...

• You want more rows.
• It's a stacking operation.
• Your tables have the same columns.
• The result is a single, longer table.

Use Merge When...

• You want more columns.
• It's a joining operation.
• Your tables share a common key/ID column.
• The result is a single, wider table.

Final Thoughts

Mastering Append and Merge is a major step up in your Power BI skills. It moves you past creating reports from a single flat file and into the world of building truly integrated data models. It's how you go from seeing what happened to understanding why it happened by connecting different pieces of your business data.

While powerful, going through these steps reveals the manual work and learning curve involved in traditional BI reporting. At Graphed, we created our AI data analyst because we believe you shouldn't have to spend your time in query editors just to see how your platforms are performing together. You can connect sources like Google Analytics, Shopify, and your CRM in a few clicks, then just ask a question in natural language, like "show me my revenue from Shopify broken down by Facebook Ad campaign." We handle all the connections, joins, and data prep automatically, delivering a real-time dashboard in seconds.