How to Combine Multiple Data Sources in Power BI

Combining data from different spreadsheets, apps, and platforms is one of the most powerful features in Power BI, turning separate datasets into a single source of truth. This article breaks down exactly how to combine multiple data sources, focusing on the two main methods: appending and merging. You'll learn the difference between them and walk through step-by-step instructions for each.

Why Combine Data Sources in the First Place?

Modern businesses run on multiple platforms. Your sales data might live in Salesforce, your ad spend in Google Ads and Facebook Ads, and your website traffic in Google Analytics. Analyzing each of these in a silo only shows you a small piece of the puzzle. Bringing them together allows you to build a complete picture of your business performance.

Combining data sources lets you:

• Get a 360-degree view: See the full customer journey by linking ad campaign costs to actual sales revenue.
• Answer complex questions: Figure out things like your true customer acquisition cost (CAC) by blending marketing expenses with new customer data from your CRM.
• Uncover deeper insights: Identify which marketing channels drive the most valuable customers, not just the most website clicks.
• Streamline reporting: Consolidate scattered data. For example, you can combine monthly sales reports stored in separate Excel files into a single master sales table for year-over-year analysis.

Power BI’s Power Query Editor is where this magic happens. It’s the data transformation layer where you can clean, shape, and combine your data before visualizing it.

The Two Main Ways to Combine Data: Append vs. Merge

Before you start clicking buttons, you need to understand the fundamental difference between appending and merging queries. Choosing the right one is essential.

What is Appending?

Think of appending as stacking tables on top of each other. When you append queries, you add the rows from one table to the end of another. This is useful when you have data of the same type that has been split into different files or tables, but shares the exact same column structure. The number of rows increases, while the number of columns stays the same.

• Common Use Case: You have monthly sales data in separate CSV files (e.g., Jan_Sales, Feb_Sales, Mar_Sales). Appending them creates a single table with all sales data for the quarter.
• The Goal: To create a longer, unified table from multiple similar tables.

What is Merging?

Think of merging as joining tables side-by-side. Merging works like a VLOOKUP in Excel, allowing you to add columns from one table to another based on a common key or matching column. The number of columns increases, while the number of rows typically stays the same as your starting ("left") table.

• Common Use Case: You have a "Sales" table with a ProductID and a separate "Products" table with ProductID and ProductName. Merging them lets you add the ProductName to your sales records so you can analyze sales by product name, not just ID.
• The Goal: To enrich a table with additional information from another table.

Let's walk through how to do both.

How to Append Queries in Power BI (Stacking Data)

For this example, imagine you have two simple CSV files: Sales_Q1.csv and Sales_Q2.csv. They both have the same columns: OrderID, Product, Date, and Revenue. Our goal is to create one table with the sales data for the first half of the year.

Step 1: Get Your Data into Power BI

First, you need to load your data sources.

1. From the Power BI Desktop Home tab, click Get Data and select Text/CSV.
2. Select your first file, Sales_Q1.csv, and in the preview window, click Transform Data. This opens the Power Query Editor.
3. Repeat the process for Sales_Q2.csv. In Power Query, click New Source > Text/CSV and select the second file.

You should now see both Sales_Q1 and Sales_Q2 listed as separate queries in the Queries pane on the left.

Step 2: Choose the Append Queries Option

With both tables in Power Query, it's time to combine them. On the Home ribbon, find the Combine group and click the dropdown arrow on Append Queries.

You have two choices:

• Append Queries: This appends the data directly into your currently selected table. It modifies one of your original queries, which can be messy.
• Append Queries as New: This creates a brand new query containing the combined data, leaving your original data sources (Sales_Q1, Sales_Q2) untouched. This is the recommended best practice.

Select Append Queries as New.

Step 3: Configure the Append

An "Append" dialog box will appear. You'll be asked if you are combining two tables or three or more.

1. Since we have just two, select Two tables.
2. For the "Primary table," select Sales_Q1.
3. For the "Table to append to the primary table," select Sales_Q2.
4. Click OK.

If you were combining more than two tables (for instance, four quarterly sales files), you would choose "Three or more tables." This gives you a different view where you can add all the relevant tables from the "Available tables" list to the "Tables to append" list.

Step 4: Review and Rename Your New Table

Power Query will create a new query, likely named Append1. You’ll see that it contains all the rows from both Sales_Q1 and Sales_Q2.

It's good practice to rename this query to something meaningful. In the Query Settings pane on the right, change the name from Append1 to Sales_H1.

You have now successfully appended your data! You can now click Close & Apply on the Home ribbon to load your results into the Power BI data model.

How to Merge Queries in Power BI (Joining Data)

Now, let's look at merging. Imagine we have two tables:

• A Sales table with TransactionID, ProductID, Quantity, and SaleDate.
• A Products table with ProductID, ProductName, and ProductCategory.

Our goal is to analyze sales by ProductName and ProductCategory, but that information is missing from the Sales table. We need to merge them using the common ProductID column.

Step 1: Get Your Data into Power Query

As before, load your Sales and Products tables into the Power Query Editor using the Get Data feature.

Step 2: Start the Merge Queries Process

Select the Sales table in the Queries pane, as this is the primary table we want to add data to.

On the Home ribbon, click the dropdown arrow on Merge Queries and select Merge Queries as New to create a combined table without altering your originals.

Step 3: Configure the Merge and Join Kind

The Merge dialog box is where you set the rules for joining your tables.

1. The top dropdown should already be set to your Sales table.
2. In the second dropdown, select the Products table.
3. Click on the ProductID column header in the Sales table preview, then click the ProductID column header in the Products table preview. The columns will highlight, indicating they are the keys for the merge.
4. Next is the Join Kind. The default, Left Outer, is the most common. It keeps all rows from the first (left) table and brings in matching rows from the second (right) table. This is exactly what we need. For beginners, Left Outer is usually what you're looking for. Other join types are more advanced and used for different scenarios, like finding records that exist in both tables (Inner Join) or in either table (Full Outer Join).
5. Click OK.

Step 4: Expand the New Column

Just like with appending, Power Query will create a new table, Merge1. Notice the last column on the right is named Products and contains [Table] in every cell. This column represents entire tables of data from the Products source that matched a given row.

To pull specific columns into our main table, click the expand icon (two arrows pointing in opposite directions) in that column's header. This opens a dropdown list of all the columns available in the Products table.

• Deselect (Select All Columns).
• Select the columns you want to add: ProductName and ProductCategory.
• Uncheck "Use original column name as prefix." If you leave this checked, your new columns will be named Products.ProductName, which is usually unnecessary.
• Click OK.

Step 5: Verify the Final Table

Your Sales table is now enriched! It has all of its original data, plus two new columns, ProductName and ProductCategory, pulled from the Products table. You can now build reports analyzing revenue by category without ever having to write a VLOOKUP formula. Don’t forget to give your new Merge1 query a descriptive name like Sales_Enriched.

Once you're done, click Close & Apply. Your new, combined dataset is ready for creating visualizations.

Best Practices for Combining Data

• Clean Before Combining: Address data quality issues like misspellings, errors, or inconsistent formatting before you append or merge. It's much easier to clean single tables than a giant, combined one.
• Check Data Types: Ensure the columns you're using to merge have the same data type (e.g., you can't join a text column to a number column). For appending, make sure corresponding columns share the same data type.
• Name Columns Consistently: When appending, Power BI combines columns based on their names. If one file has a Revenue column and another has Sales_Amount, they will be treated as two separate columns, creating messy, sparse data. Rename them to match first.
• Don't Be Afraid of the "New" Query: Using "Append as New" and "Merge as New" is a safe way to experiment. It keeps your original data pipelines clean and makes troubleshooting much easier if something goes wrong.

Final Thoughts

Learning how to combine data separates casual users from true Power BI analysts. Appending stacks data with identical structures to create longer tables, while merging joins tables side-by-side using a common key to enrich records with more columns. Mastering both techniques within Power Query will unlock far deeper and more valuable insights for your business.

While Power BI offers incredible control, the process of connecting, cleaning, and combining data sources still involves a lot of manual steps and a steep learning curve. We know that marketing and sales teams often don't have dedicated data analysts to manage this process. That's why we created Graphed. It automates the entire reporting workflow by letting you connect your sources in a few clicks and build dashboards just by describing what you need in plain English. Instead of stepping through Power Query settings, you can simply ask, "show me top-selling products by category for Q1," and get a real-time answer instantly.