How to Append Excel Files in Power BI

Manually copying and pasting data from monthly or weekly Excel reports into a single "master" spreadsheet is a tedious and error-prone ritual for many people. If you're using Power BI for your reporting, there's a much smarter, automated way to handle this. You can connect Power BI directly to a folder and have it automatically combine all your files for you.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

This article will walk you through the surprisingly simple steps to append multiple Excel files in Power BI. Once you set this up, you'll never have to manually consolidate data again - just drop a new file into the folder and hit refresh.

What Does "Append" Mean in Power BI?

Before we jump in, it’s important to understand what "appending" files means. Think of it as stacking data on top of each other. If you have a separate Excel file for sales data for January, February, and March, appending them creates one long list, or a single table, containing the data from all three months.

This is different from merging (or joining), which is about adding new columns of related data. For example, you might merge a sales table with a customer information table to add the customer's location to each sale. Appending adds rows, merging adds columns.

Appending is incredibly useful when you're collecting the same type of data over time, such as:

• Monthly sales or revenue reports
• Weekly marketing campaign performance exports
• Daily website traffic data
• Quarterly team performance metrics

By appending these files in Power BI, you can analyze long-term trends without spending hours on mind-numbing data prep.

Before You Begin: Prepare Your Excel Files

The secret to making this process work smoothly is consistency. Power BI needs to know that all the files it’s combining have the same layout. If the structure is inconsistent, the process will break.

Follow these two simple rules for your source files:

1. Keep the Structure Identical

Every Excel file you want to append must have the same structure. This means:

• Identical Column Headers: The column names must be exactly the same in every file. "Sale Amount" is not the same as "Sale_Amount" or "Amount".
• Same Number of Columns: Every file should contain the same columns in the same order.
• Consistent Data Location: The data should be on the same sheet name (e.g., "Sheet1") or in a named Excel Table in each file. Using a named table is best practice, but a consistent sheet name works perfectly fine.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Create a Dedicated Folder

Place all the Excel files you want to combine into a single, dedicated folder on your local drive or on a shared drive like OneDrive or SharePoint. Power BI will point to this folder as the data source.

For example, you could create a folder on your Desktop named "Monthly Sales Reports" and place Sales_Jan_2024.xlsx, Sales_Feb_2024.xlsx, and so on, inside it.

Step-by-Step Guide: How to Append Excel Files in Power BI

Once your files are organized, it's time to connect Power BI. This process is handled within the Power Query Editor, which is Power BI's built-in tool for data transformation.

Step 1: Connect to the Folder

First, open a blank Power BI Desktop report. On the Home tab, click Get data and then select More...

In the Get Data window, type "Folder" in the search bar or select it from the list of connectors. Click Connect.

You'll then be prompted to browse for your folder. Click Browse..., navigate to the folder where you stored your Excel files, and click OK.

Step 2: Combine & Transform the Files

After connecting to the folder, Power BI will show you a metadata view listing all the files within it. You'll see details like the file name, date created, and folder path.

To start the appending process, click the Combine & Transform Data button at the bottom of the window.

Step 3: Select the Sample File

Next, Power BI needs a template to understand how to process each file. It will use the first file in the folder as a "sample file" to figure out the structure.

A new window called "Combine Files" will appear. On the left, it shows the sample file (e.g., Sales_Jan_2024.xlsx). Select the object you want to extract from each file - this will typically be the specific worksheet (like Sheet1) or a named Excel Table that contains your data.

You’ll see a preview of the data on the right. Once it looks correct, click OK.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Review and Clean in Power Query

Power BI will now open the Power Query Editor. It automatically performs several actions behind the scenes:

1. It creates a query based on your sample file to define the steps needed to transform one file (e.g., promoting headers, changing data types).
2. It applies this exact same set of steps to every single file found in the source folder.
3. It stacks (appends) all the processed data into one large table.

The result is a single table containing all your data. You'll notice Power Query also added a new column at the beginning called Source.Name. This column contains the original filename for each row, which is incredibly useful for filtering or separating your data by month, week, or whatever your file naming convention is.

Now is a good time to do some basic data cleaning:

• Check Data Types: Make sure columns are formatted correctly. For example, click the icon in the column header to set your Date column to a Date type, your Revenue column to Currency or Decimal Number, and so on.
• Remove Unnecessary Columns: If you don’t need the original filenames, you can right-click the Source.Name column header and select Remove.
• Manage Errors or Null values: Scan your columns for any errors or unexpected blank rows and use the filters to investigate and remove them if needed.

Step 5: Load Your Data

Once you’re happy with how the combined data looks, click the Close & Apply button in the top-left corner of the Power Query Editor. Power BI will load the new, unified table into your data model. You can now build charts, tables, and dashboards using data from all your files at once!

The Power of Automation: Just Add New Files and Refresh

The real magic happens next month. You don't need to repeat any of these steps.

When you have a new file (e.g., Sales_April_2024.xlsx), all you have to do is drop it into the same folder you connected to Power BI earlier.

Then, open your Power BI report and click the Refresh button on the Home tab. Power BI will re-check the folder, find the new file, automatically apply all the same cleaning and transformation steps you defined, and append its data to your existing table.

This "set it and forget it" approach saves enormous amounts of time and drastically reduces the risk of copy-paste errors that plague manual reporting processes.

Troubleshooting Common Problems

Things don't always go perfectly. Here are a few common issues you might run into and how to fix them:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Error: "The column '[Column Name]' of the table wasn't found."

This is the most frequent error. It means at least one of your Excel files has a different column name or order than the sample file. Open your Excel files and ensure every single column header is identical across all of them.

Error: Mixed Data Types

If Power BI expected a number but found text in a column from one of the files (e.g., someone wrote "N/A" in a sales column), it may cause an error for that row or force the entire column to be treated as text. The fix is to clean the source data in the original Excel file or use Power Query's Replace Errors feature to fix it during the transformation step.

Data From Unwanted Files Appears

Sometimes, temporary Excel files (often starting with ~$) or other irrelevant files find their way into your folder. To prevent Power BI from trying to import them, you can filter the file list in Power Query. Go to the transformation steps pane on the right, edit an early step, and add a filter on the filename column to only include files that contain .xlsx and do not start with ~.

Final Thoughts

Using Power BI's "Get Data from Folder" feature is a game-changer for anyone regularly consolidating similarly structured reports. By automating the process of appending files, you eliminate tedious manual work, prevent common data entry errors, and free up your time to focus on analyzing the insights within your data, not just preparing it.

While mastering tools like Power BI provides a powerful way to streamline these workflows, we know setting up data pipelines can still be complex, especially with data scattered across many different marketing and sales platforms. At Graphed you only have to connect your data sources — like Google Analytics, Shopify, or Facebook Ads — with just a few clicks and then ask for what you need in plain English. We turn your request into a real-time, interactive dashboard instantly, so you can spend less time wrangling data and more time making decisions.