How to Merge Multiple Excel Files in Power BI

Manually copying and pasting data from dozens of monthly Excel reports into one master spreadsheet is a painful, error-prone ritual. This common "reporting" process takes hours away from actual analysis. Fortunately, Power BI has a simple, powerful feature designed specifically to solve this problem, letting you merge all your Excel files automatically.

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 tutorial will walk you through, step-by-step, how to use Power BI's "From Folder" connector to combine multiple Excel files into a single, clean dataset. You'll learn how to set up your files, connect to them in Power BI, and let the Power Query editor do the heavy lifting for you.

First, Why Merge Files in Power BI?

Before jumping into the "how," let's quickly cover the "why." Consolidating your Excel files directly inside Power BI is a massive time-saver and eliminates the manual drag of copy-pasting. This method is perfect for anyone who regularly works with files that follow a consistent format, such as:

• Monthly or weekly sales reports
• Daily performance exports from a marketing platform
• Regional performance spreadsheets
• Inventory logs from different warehouses

Instead of wrestling with a massive, unwieldy master spreadsheet that breaks every other week, you let Power BI handle the consolidation. More importantly, this process is repeatable. When a new file comes in next month, you don't repeat the process, you just drop the file in the designated folder and hit "Refresh" in Power BI. The report automatically updates with the latest data.

The Pre-Merge Checklist: Prepping Your Excel Files

A little bit of preparation goes a long way and saves you from future headaches. To ensure the process is as smooth as possible, your Excel files should be organized and consistently formatted. Make sure you check off these three simple requirements before you start.

1. Create a Dedicated Folder

Gather all the Excel files you want to merge and place them into a single, dedicated folder on your computer or a shared drive like OneDrive or SharePoint. Power BI will connect to this folder, not the individual files, so everything needs to be in one place. Don't put any other unrelated files in this folder, as it can cause errors during the import process.

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. Ensure Consistent File Structure

This is the most critical step. Power BI uses the first file it finds as a template to understand how to process all the others. For this to work, every Excel file in the folder needs to have the exact same structure. Pay close attention to:

• Column Headers: The column headers must be identical across all files. For example, if one file has "Customer ID" and another has "CustomerID," Power BI will treat them as two different columns. Casing and spacing matter, so "Sales Rep" and "Sales rep" are considered different.
• Data Format: The data should be organized in a simple tabular format (rows and columns). The headers should be in the first row, with the data starting directly below them. Avoid any merged cells, titles, or summaries above your main data table.

3. Use Consistent Sheet Names

Your data likely lives on a specific sheet within each Excel workbook. It's best practice to give this sheet the same name in every file, like "SalesData" or "Jan2024". While it's possible to work around inconsistent sheet names, it adds complexity. Sticking to a consistent name will make the process in Power BI far simpler.

Once your folder is ready and your files are standardized, you're ready to jump into Power BI.

Step-by-Step: Connecting and Merging Files in Power BI

Now for the fun part. We'll connect to our folder and let Power BI work its magic. Open a blank Power BI Desktop file to get started.

Step 1: Get Data from a Folder

In the "Home" tab of the Power BI ribbon, click on Get Data. This opens a dropdown menu with common data sources. Since we're connecting to a folder, select More... at the bottom. In the pop-up window, use the search bar to find the Folder connector or select File > Folder from the list and click Connect.

Step 2: Provide the Folder Path

Next, Power BI will ask you for the folder path. You can click Browse... and navigate to the folder you created in the preparation step. Once you've selected it, click OK.

You'll now see a preview window showing the metadata for each file in your folder. This includes information like the file name, extension, date created, and so on. This isn't your Excel data - not yet. It's just a list of the files Power BI found. To start combining them, you need to use the Power Query Editor.

Click on Transform Data at the bottom right. This is almost always the best option, as it gives you the flexibility to clean and shape the data before loading it into your report.

Step 3: Combine Files in Power Query

The Power Query Editor is where all data transformation happens in Power BI. You'll see the file metadata again, arranged in columns. The key column here is the Content column, which contains the actual binary data of each Excel file. To merge them, find the Combine Files button in the header of the Content column. It looks like two small arrows pointing down.

Click this button. Power BI will now begin the combining process by asking you to define the template for the merge.

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: Select a Sample File and Sheet

A new "Combine Files" dialog box will appear. Here, Power BI is asking for two things:

1. Sample File: You can select a sample file to use as a template. Usually, leaving it as the "First File" is perfectly fine.
2. Sheet/Table: Power BI will inspect the sample file and show you the available sheets or tables within it. Select the sheet that contains your data. You'll see a small preview on the right. This is why consistent sheet names are so important - Power BI will look for a sheet with this exact name in every other file.

Click OK. At this point, Power BI does something brilliant. It goes to work in the background, applying the steps needed to connect to the sample sheet to every single file in your folder and then stitches all the resulting data together into one unified table.

Step 5: Review the Combined Data

After a few moments, the Power Query Editor will refresh, and you'll see your combined data in a single table! All the columns from your Excel files are there, plus an extra one on the far left called Source.Name. This column is automatically added by Power BI and is incredibly useful, as it contains the original file name where each row came from. This allows you to easily filter, group, or analyze your data by month, region, or whatever distinction your file names represent.

Your files are now merged! At this stage, you can perform any additional data cleaning you need, such as checking data types, renaming columns, or filtering out unnecessary rows. When you're happy with the result, click Close & Apply in the top-left corner to load your new table into the Power BI data model.

Troubleshooting Common Issues

The "From Folder" method is robust, but you might occasionally run into snags, especially if your file structures aren't perfectly consistent. Here are a few common problems and how to fix them.

Problem: Mismatched Column Headers

Symptom: You see duplicate columns in Power Query, like Sales and Sale Amount, when they should be one column.

Solution: This happens when column headers aren't identical across your files. The best solution is to go back to the source Excel files and correct the headers to be consistent. If you can't edit the source files, you can handle this in Power Query by selecting the mismatched columns and using the Merge Columns feature, though this is a more advanced workaround.

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

Problem: Incorrect Data Types

Symptom: Numbers are being treated as text, or dates are not recognized correctly.

Solution: Power BI tries its best to automatically detect data types, but it's not always perfect. In the Power Query Editor, check the icon in each column header. A ABC icon means "text," 123 means "whole number," and a calendar icon means "date." To change a data type, simply click the icon and select the correct type from the dropdown menu.

Problem: "Key didn't match any rows in the table" Error

Symptom: The query fails with an error message like "Expression.Error: The key didn't match any rows in the table." when you try to combine files.

Solution: This error almost always means that the sheet name Power BI is looking for (based on your sample file) doesn't exist in one of the other files. For example, if your sample uses the sheet "Report" but another file uses "Sheet1," the query will fail. Go back and check your files to make sure the data sheet has the exact same name in every single workbook.

Living in the Future: Adding and Refreshing New Data

The real power of this method shows up a month from now. When you receive a new file (e.g., your sales report for next month), you don't need to rebuild anything. Simply follow these two steps:

1. Drop the new Excel file into the same folder.
2. Open your Power BI report and click the Refresh button on the Home tab.

That's it. Power BI will re-run the query, find the new file, apply all the same transformation steps, and append its data to your existing table. Your dashboards and visuals will update instantly. This one-click refresh saves you from ever having to manually copy-paste data in Excel again.

Final Thoughts

Learning how to merge multiple Excel files using Power BI's folder connector is a game-changer for anyone who manages recurring reports. It automates one of the most tedious parts of data preparation, giving you back valuable time to focus on creating visuals, finding insights, and making decisions.

While this is a fantastic feature for consolidating flat files like Excel and CSVs, at Graphed we aim to eliminate this type of manual work entirely. We let you connect directly to live data sources - like Google Analytics, Shopify, Salesforce, and Facebook Ads - with one-click integrations. Instead of downloading files from these platforms just to upload them somewhere else, we give you a way to build real-time, automated dashboards by simply describing what you want to see in plain English. Your data stays current, and you stay focused on strategy instead of report wrangling.