How to Combine Multiple Excel or CSV Files in Power BI (Append Query Guide)

Imagine you're a business owner, manager, or data analyst in India, regularly receiving sales data. You have separate Excel files for 2015, 2016, and 2017. To analyze overall trends, you need to combine this data into one comprehensive table. Manually copying and pasting rows from each file into a master sheet is tedious, error-prone, and a huge time sink. This guide will show you how to combine multiple Excel or CSV files in Power BI using Power Query's 'Append' feature, transforming hours of manual work into a quick, automated process.

Instead of manual effort, Power Query allows you to bring all your datasets together into one single, unified table automatically. As one of our instructors explains, "I have this data for 2015, 2016, and 2017. I'm going to bring all three together by appending the queries, and you will have one single dataset that contains all this information together."

The Common Problem: Too Many Files, Not Enough Time

Whether it's monthly sales reports, quarterly financial statements, or daily operational logs, data often arrives in fragmented files. Consolidating these files manually in Excel means opening each one, copying rows, pasting them into a master sheet, and then repeating the process. This isn't just inefficient; it significantly increases the risk of errors, especially when dealing with large volumes of data or frequent updates. Power BI, through its powerful Power Query Editor, offers a robust solution to automate this process, ensuring accuracy and saving valuable time.

Append vs. Merge: What's the Real Difference in Power Query?

Before we dive into the steps, it's important to clarify a common point of confusion: the difference between 'Append' and 'Merge' in Power Query. While both are used to combine data, they serve distinct purposes:

• Append Queries: This operation stacks rows from multiple tables on top of each other. It's used when you have files with the same column structure but different rows of data (e.g., sales data for different years, where each file has 'Date', 'Product', 'Sales Amount' columns). This is what you'll use to combine multiple Excel files in Power BI when you want to create one long list of transactions.
• Merge Queries: This operation adds columns from one table to another, based on a common column (like performing a VLOOKUP in Excel). It's used when you have related data in separate tables (e.g., a sales table and a product information table, both containing a 'ProductID' column). If you're struggling with VLOOKUP Hell in Excel, understanding Merge queries is a key step towards a more efficient data model.

For our scenario of combining 2015, 2016, and 2017 sales files, where each file has the same columns but different rows of data, 'Append Queries' is the correct function.

Step-by-Step: How to Combine Multiple Excel Files in Power BI Using Append Query

Let's walk through the process of appending your 2015, 2016, and 2017 sales data in Power BI Desktop.

1. Get Data and Select the First File

1. Open Power BI Desktop.
2. From the 'Home' tab in the ribbon, click 'Get Data'.
3. Choose 'Excel Workbook' if your files are .xlsx or .xls. If they are .csv files, select 'Text/CSV'.
4. Navigate to the folder containing your files and select your first file (e.g., 'Sales_2015.xlsx'). Click 'Open'.
5. In the 'Navigator' window, select the specific sheet or table you want to import (e.g., 'Sheet1' or 'Sales Data').
6. Click 'Transform Data' to open the Power Query Editor. This will load your first dataset as a new query.

2. Load Remaining Files into Power Query

Now, repeat the 'Get Data' process for your 'Sales_2016.xlsx' and 'Sales_2017.xlsx' files. For each file:

1. While still in the Power Query Editor, go to the 'Home' tab and click 'New Source'.
2. Select 'Excel Workbook' (or 'Text/CSV') and import 'Sales_2016.xlsx'.
3. Repeat for 'Sales_2017.xlsx'.

You should now see three separate queries (e.g., 'Sales_2015', 'Sales_2016', 'Sales_2017') listed in the 'Queries' pane on the left side of the Power Query Editor.

3. Use the 'Append Queries' Feature

With your individual queries loaded, it's time to combine them:

1. Select your first query (e.g., 'Sales_2015') in the 'Queries' pane. This will be your primary table.
2. Go to the 'Home' tab in the ribbon, locate the 'Combine' group, and click on the 'Append Queries' dropdown.
3. You'll see two options: 'Append Queries' and 'Append Queries as New'. As our instructor highlights, you can "select a master file which will have an append queries as new," because "I want to create a new data file." For this scenario, where we want a new, combined table, select 'Append Queries as New'. This creates a fresh query containing all your combined data, leaving your original files untouched.

4. Select 'Three or More Tables' and Add Files

1. In the 'Append' dialog box that appears, you'll be prompted to choose between 'Two tables' or 'Three or more tables'. Since we're combining 2015, 2016, and 2017 data, select 'Three or more tables'.
2. In the 'Tables to append' section, your currently selected query (e.g., 'Sales_2015') will be listed under 'Primary table'.
3. From the 'Available tables' list on the left, select 'Sales_2016' and then click 'Add >>'.
4. Repeat this for 'Sales_2017'. Ensure all three tables are moved to the 'Tables to append' list on the right.
5. Click 'OK'.

5. Verify the Combined Data

A new query, typically named 'Append1', will appear in your 'Queries' pane. Click on it to preview the combined data. Scroll through to confirm that rows from 2015, 2016, and 2017 are now stacked together in a single table. You will now have a single dataset that contains all the information from your three separate files. Rename this query to something descriptive like 'Combined Sales Data' for clarity.

Once satisfied, click 'Close & Apply' from the 'Home' tab to load the combined data into your Power BI data model, ready for analysis and visualization. For a deeper dive into these and other data transformation techniques, consider Juno's Power Query in Power BI course.

Common Errors and How to Fix Them

Even with a powerful tool like Power Query, issues can arise when appending files. Here are some common problems and their solutions:

• Mismatched Column Names: If your files have slightly different column names (e.g., 'Product ID' in one, 'ProductID' in another), Power Query will treat them as separate columns, resulting in null values in the combined table where data is missing.
  Fix: Before appending, rename columns in the individual source queries within the Power Query Editor to be identical. Right-click on the column header and select 'Rename'.
• Inconsistent Data Types: Appending files with inconsistent data types for the same column (e.g., 'Sales' as text in one file, number in another) can lead to errors or incorrect data aggregation.
  Fix: Ensure data types are consistent for all columns across all source queries. Select the column, go to the 'Transform' tab, and use the 'Data Type' dropdown to set the correct type (e.g., 'Whole Number', 'Decimal Number', 'Text').
• Extra Columns in Some Files: If one file has an extra column not present in others, it will simply appear as a new column in the appended table, with nulls for files that don't have it. While not strictly an error, it can make your data messy.
  Fix: If you don't need the extra column in your final combined dataset, remove it from the individual source query *before* appending. Right-click on the column header and select 'Remove'.

Addressing these issues in the Power Query Editor ensures your combined dataset is clean and accurate. If you frequently encounter messy data, learning how to clean messy Excel data in Power BI is an invaluable skill.

Your Next Step: Automating Your Reporting

While manually appending individual files is effective for a small, fixed number of files, imagine if you receive new sales data every month or quarter. Manually adding each new file to your append query would quickly become tedious again. Power Query offers an even more powerful solution for this: 'Get Data from Folder'.

This advanced technique allows you to point Power BI to a folder containing all your Excel or CSV files. Power Query then automatically combines *all* files within that folder, even new ones added later, with just a few clicks. This truly automates your data consolidation process, making your reporting dynamic and significantly reducing manual effort. Mastering this and other advanced Power Query techniques is essential for any data professional looking to build robust, automated reports and move beyond manual Excel data cleaning.