Append and Combine in Power Query

In today’s data-driven world, working with multiple datasets from various sources is common. Whether you're consolidating sales reports, managing project updates, or tracking performance across departments, Power BI’s Power Query Editor provides powerful tools to streamline data management. Append and Combine are the two essential options that help simplify and automate this process.

In this blog, we’ll explore what these options mean, how they work, and where you can apply them—especially in real-world reporting scenarios like weekly or daily file consolidations.

What is Append in Power Query?

Appending in Power Query means combining two or more tables with the same column structure into one. It’s like stacking spreadsheets with identical headers on top of each other. For instance, if you have monthly sales data split across different Excel sheets, you can append them into one consolidated table.

   When tables that don't have the same column headers are appended, all column headers from all tables are appended to the resulting table. If one of the appended tables doesn't have a column header from other tables, the resulting table shows null values in the respective column, as shown in the above image.

    There are two options to append:

1.Append queries - appends other tables to your current query.

 For example: You have two tables, A and B. You select Append queries in table A, and request to append table B. Your table A query will now have an appended table that contains aggregated data from A and B. Your table B query is unchanged.

• Select your base query.
• Go to Home > Append Queries.
• Choose the second table to combine it directly into the existing query.

2.Append queries as new - appends other tables to a new query.

For example: You have two tables, A and B. You select Append queries as new in table A, and request to append table B. You now have a new query called Append1 that contains an aggregated table from A and B. Both your table A and table B queries are unchanged.

• Go to Home > Append Queries > Append Queries as New.
• Choose the tables you want to append.
• A new table will be created combining them.

Example: Weekly Sales Report – Append Files:

        You receive a weekly Excel file from different sales regions. Each file contains a table with the same structure.
Import all files:

Week 1:

Week 2:

Week 3:

Week 4:

=>Home > Append Queries as New.

A unified “All Week Sales of June” table that includes data from all weekly files, perfect for dashboards or time-based analysis.

What is Combine in Power Query?

 Combine Files is a Power Query feature that allows you to automatically import and consolidate multiple files from a folder as long as they all share the same structure (same columns, same layout). It’s mostly used when you’re working with repetitive data inputs like monthly reports, daily logs, or regional templates.

Step 1: Organize Your Files-Make sure all your source files are saved in one folder and Each file should follow the same structure (same columns, same data types).

Step 2: Get Data from Folder.

 Go to the Home tab → Click Get Data → Choose Folder.

Step 3: View and Combine Files

 A preview window opens showing file names.

• Click Combine & Transform Data.
• Power Query automatically:

1. 1.  Picks the first file as a sample.
   2. Shows a preview of the sample file.

• Click OK to proceed.

Step 4: Power Query Creates Helper Queries

Power Query will generate:

• A sample query (Sample File).
• A function (Transform File) based on your sample.
• A main query that automatically combines all files using that function.

 Step 5: Clean & Transform Your Combined Data

You’ll now see a single combined table in Power Query.

• Rename the query (e.g., Combined Sales Data).
• Remove unwanted columns (e.g., metadata).
• Use “Source Name” column to track which file each row came from.
• Filter, transform, or add calculated columns as needed.

 Step 6: Load the Data

Once your data is cleaned:

• Click Close & Load (or Close & Apply if in Power BI).
• Your combined data is now available in Power BI for reporting and analysis.

This method is extremely helpful when you drop new files into a folder every day/week, and Power BI updates the data automatically when refreshed.

Example: Daily Expense Report – Combine Files:

 A freelancer or small business owner records daily expenses and save it in one folder.

Use Power Query > Get Data > Folder

=>Power Query will combine all rows into one clean, daily expense dataset.

Dropping daily expense reports into a folder, and Power BI will instantly merge them into one clean, dynamic dataset.

Combine & Transform:

  After you combine files using the Combine feature, Power Query allows you to transform the data—clean, format, or add calculated columns.

For example, after combining files:

• Remove unwanted columns.
• Filter out blank or duplicate rows.
• Create a “Source File” column to track where the data came from.

This ensures your data is not only merged, but also structured correctly for downstream analysis.

Related Offerings

Combine vs Append – What’s the Difference?

A real-world case where both Append and Combine are used together:

Example: Multi-Branch Sales Consolidation

You manage a company with multiple sales branches X, Y AND Z.

Each branch:

• Saves its daily sales as separate files in its own folder.
• At the end of the month, you need to consolidate all daily sales files across all branches into a single master table.

Step 1: Combine Daily Files Inside Each Branch Folder

1. For X, go to:
• Get Data > Folder
• Select the X folder
• Combine & Transform → now you get X Sales query
2. Repeat the same for Y and Z folders
   → Now you have three queries:
• X Sales
• Y Sales
• Z Sales

These queries each contain all combined daily files from one branch.

 Step 2: Append the Combined Queries

Now that you have three combined tables, you’ll append them to get one master sales table.

1. Go to Home > Append Queries > Append Queries as New
2. Select the three queries: X Sales, Y Sales and Z Sales.
3. Name the result: All Branches Sales

 You now have one single table with all daily sales from all branches.

 Step 3: Enhance the Data

• Add a column to each query before appending like Branch = "x" so you know the source.
• Clean and transform the final appended table.
• Load it into Power BI and create dashboards like:

1. 1. Branch-wise monthly sales
   2. Top-selling items across locations
   3. Daily trends per branch

Understanding how to use Append and Combine in Power Query can significantly reduce manual data handling, especially when working with repetitive file formats. By automating data collection and preparation, you can focus more on analysis and decision-making.

Whether you’re dealing with a stack of weekly reports or merging CSVs from multiple sources, mastering these tools will save hours of time and effort.

Need Help Building Smarter Power BI Dashboards?

At Team Academy, we offer hands-on Power BI training and dashboard development services tailored for professionals and organizations. Learn how to automate your data workflows using tools like Append, Combine, and Power Query from scratch.

 Have questions? Contact us.

Download PowerBI Visualisation Ebook