How to Upload Multiple Excel Files in Power BI

Tired of the monthly ritual of copying and pasting data from dozens of Excel files into one giant "master" spreadsheet? This article shows you how to automate that entire process by connecting Power BI directly to a folder, allowing it to combine multiple Excel files into a single, report-ready dataset that updates automatically.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why You Should Stop Manually Combining Excel Files

Before diving into the "how," let's quickly cover the "why." If you're manually combining weekly sales reports, monthly ad spend exports, or regional performance files, you’re likely familiar with the headaches involved. It's not just tedious, it's a flawed process.

• It’s Incredibly Time-Consuming: The time you spend on manual data entry is time you aren’t spending on analysis. The process of opening files, copying data, pasting it, and fixing formatting can easily consume hours every single week.
• It’s Prone to Human Error: Did you copy the right cells? Did you accidentally paste over last month's data? Is a formula broken somewhere? A single slip-up can compromise your entire report and lead to poor decision-making.
• It Doesn’t Scale: This method might work with 3-4 files, but it collapses under the weight of 10, 20, or 100 files. As your business grows, your manual process becomes a significant bottleneck.

Automating this with Power BI solves all three problems. You set it up once, and from then on, you simply hit "Refresh."

The Easiest Method: Combining Files from a Folder

The most common and straightforward way to combine multiple Excel files is to store them all in a single folder. Power BI can connect to that folder, identify all the files, and merge them for you. This works best when all your files have an identical structure - the same columns in the same order.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Get Your Files and Folder Ready

Preparation is the most important step. To ensure Power BI can combine your files without issues, follow these simple rules:

• Use a Dedicated Folder: Create a specific folder just for the Excel files you want to combine. Don’t store other random documents in here. For example, create a folder named "Monthly Sales Reports."
• Ensure a Consistent File Structure: This is the golden rule. Every Excel file in the folder needs to have the same layout. The data you want to combine should be on the same sheet name (e.g., "Sheet1" in every file) and have identical column headers (e.g., "Date," "Region," "Sales Amount").
• Use Clear Naming Conventions: While not strictly required by Power BI, naming your files consistently (e.g., Sales_Data_Jan_2024.xlsx, Sales_Data_Feb_2024.xlsx) will make it much easier for you to manage your data.

Step 2: Connecting Power BI to the Folder

With your folder prepped, it's time to fire up Power BI Desktop.

1. Navigate to the Home tab on the ribbon.
2. Click Get Data > More...
3. In the Get Data dialog box, select Folder from the list of connectors and click Connect.
4. Click Browse... and navigate to the folder you created in Step 1. Select it and click OK.

Power BI will now show you a preview window listing a table of metadata about each file in the folder, such as the file name, date modified, and folder path.

At the bottom of this window, you'll see three options: Load, Transform Data, and Combine. You want to choose Combine & Transform Data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Combining the Files in Power Query

Clicking "Combine & Transform Data" launches the Power Query Editor, which is where the real magic happens.

1. Select a Sample File: Power BI will ask you to choose a "sample file." It uses the structure of this file as a template to figure out how to process all the other files in the folder. Pick the first file from the dropdown.
2. Choose the Data Object: On the left side of the window, you'll see the objects within your sample Excel file (e.g., sheets or tables). Select the specific sheet or table that contains the data you need. Power BI will show you a preview on the right.
3. Click OK.

Power BI will now perform a series of automatic steps on your behalf. In the Power Query Editor window, you'll see a query that represents your final, combined table. It looks just like a single spreadsheet, but it's actually the merged data from every file in your folder! Notice it even adds a column called Source.Name, which contains the original file name for each row. This is incredibly useful for verification and analysis.

You can now perform any additional transformations you need, like changing data types, removing columns, or adding custom columns. When you're finished, a quick click on Close & Apply in the top left corner loads that data into your Power BI report.

Handling Files with Different Column Orders

What if one month's report has columns A, B, C and the next month's has B, A, C? The standard “Combine Files” function might get confused because it combines based on column position by default. The good news is, there's a more manual but robust way to handle this in Power Query.

Instead of clicking "Combine & Transform Data" in Step 2, click Transform Data. This takes you into Power Query with just the initial list of files.

1. Add a Custom Column: Go to the Add Column tab and click Custom Column.
2. In the dialog box, name the new column "GetData" (or anything you like).
3. In the formula box, type:
4. Click OK. This creates a new column containing a Table object for each file.
5. Expand the Data: Click the expand icon (two arrows pointing in opposite directions) on the GetData column header. A dropdown will appear with the names of properties like Name, Data, Item, and Kind. Deselect everything except Data and untick "Use original column name as prefix." Click OK.
6. Expand a Second Time: You'll now see another Data column with the expand icon. Click it again. This time, it will show you the actual column headers from your Excel files (e.g., "Date," "Region," etc.). Make sure all are selected and click OK.

This method manually drills into each file and expands the data based on column headers, not position. This makes your query far more resilient to slight changes in file structure over time.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Best Practices and Pro Tips

To make your life even easier, keep these pointers in mind:

• The Beauty of Refreshing: The most significant benefit of this method happens next month. When you get a new file (e.g., Sales_Data_Mar_2024.xlsx), just drop it into your dedicated folder. Then, open your Power BI report and click the Refresh button. Power BI will automatically repeat all your steps, pick up the new file, and append it to your data model. No more copy-paste!
• Handle Data Types Carefully: After combining, Power BI often sets all columns to a "Text" or "Any" data type. Review each column in Power Query and set the correct type (e.g., Date, Whole Number, Decimal Number). This is critical for accurate calculations and visualizations.
• Promote Headers: Pay close attention to the Promote Headers step in your "Applied Steps" pane. Occasionally, Power BI might miss this, leaving you with "Column1," "Column2" as headers. If this happens, you can easily find the Use First Row as Headers button on the Home or Transform tab.
• Filter Out Unwanted Files: Before you combine, you can use the filter button on the Name column in Power Query to exclude certain files. For example, you could filter to only include files that contain ".xlsx" to avoid temporary files that Word or Excel sometimes create.

Final Thoughts

Mastering Power Query's ability to combine files from a folder is one of the biggest time-savers for anyone working with routine reports. By setting up this automated process one time, you eliminate hours of manual data wrangling, dramatically reduce the risk of human error, and build a scalable reporting system that grows with your business.

Learning the ins and outs of tools like Power BI is incredibly valuable, but we understand an analyst's real goal is to find insights, not get stuck on technical steps. The challenges of data prep are a big reason why we created Graphed. We connect directly to your data sources like Google Analytics, Shopify, or even a Google Sheet, and let you create entire dashboards using simple, natural language. Instead of building a complex query, you can just ask, "Show me my total sales by month from all my Excel reports" and get an immediate, real-time visualization.