How to Load Multiple Excel Files in Power BI

Tired of importing your Excel reports into Power BI one by one? If you're managing daily, weekly, or monthly data, this repetitive process can eat up hours of your time. This guide will show you how to use Power BI's folder connector to import and combine hundreds of Excel files at once, turning a tedious task into a one-time setup that you can refresh with a single click.

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

Why Combine Multiple Excel Files in Power BI?

Loading files individually is more than just tedious - it’s inefficient and prone to errors. When you connect Power BI to a folder instead of a single file, you build a scalable and automated workflow. Imagine you have monthly sales reports, each in a separate Excel file for January, February, March, and so on. Instead of adding a new data source every month, you can just drop the new file into your designated folder and hit "Refresh" in Power BI. All the new data will be added automatically, with all your data preparation steps applied instantly. This method saves time, ensures consistency, and allows your reports to grow with your data without extra work.

Step 1: Get Your Excel Files in Order

Before you even open Power BI, a little preparation will save you a lot of headaches. The magic of combining files from a folder relies on one crucial thing: consistency. Power BI needs to treat each file as a piece of the same puzzle, so the pieces have to fit together properly.

This means each Excel file you want to combine should have the exact same structure. Pay attention to the following:

• Consistent Column Headers: The names of your columns must be identical in every file. A column named "Sales Amount" in one file and "Sales" in another will cause errors.
• Matching Column Order: While Power BI is smart enough to handle some differences, keeping the columns in the same order dramatically reduces the chances of a mistake.
• Consistent Data Types: Ensure that a column meant for dates contains only dates in every file, and a column for sales figures contains only numbers.
• Standardized Sheet Names: Name the relevant worksheet or tab the same in every Excel workbook (e.g., "Monthly Data" or "Sheet1"). When you combine the files, Power BI will need to know which sheet to pull from, and it will use the name you specify for every file.

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

Create a Dedicated Folder

Once your files are standardized, create a new folder on your computer or a shared drive like OneDrive or SharePoint. Move all the Excel files you want to combine into this single folder. And - this is important - make sure only the files you want to import are in there. Any other stray files, notes, or temporary documents could cause your query to fail down the line.

Step 2: Connecting Power BI to Your Folder

With a folder full of cleanly structured Excel files, you're ready to jump into Power BI Desktop. The process starts by pointing Power BI to your folder instead of an individual file.

Follow these steps:

1. On the Home ribbon in Power BI Desktop, click on Get Data. From the dropdown, select More... near the bottom.
2. This opens the Get Data window, which shows all the possible data sources you can connect to. Select Folder from the list and click Connect. (You can also find it under the "File" category).
3. You'll be prompted to provide the folder path. You can copy and paste the path directly or click Browse... to navigate to your dedicated folder.
4. Click OK.

At this point, Power BI will show you a preview window. But instead of showing your sales data, it displays metadata - information about the files inside your folder. You’ll see a list of file names, extensions, date accessed, and so on. This is perfectly normal. This screen confirms that Power BI found your files, and now you need to tell it how to combine them.

Step 3: Combining and Cleaning Your Data in Power Query

This is where the real work happens. From the file metadata preview, you should see a few options at the bottom. Do not click Load. Instead, click the Combine & Transform Data button. This will launch the Power Query Editor, the engine for all data preparation in Power BI.

Choosing "Combine & Transform Data" tells Power BI that you want to open each file, extract the data, and stack it all together into one unified table.

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

The "Combine Files" Dialog

After you click "Combine & Transform", Power BI will open a new dialog box. Here, it asks for a "sample file" to use as a template. Essentially, Power BI is saying, "I see all these files. Show me how to interpret them by picking one, and I'll apply the same logic to all the others."

In this window, you will need to:

1. Choose a Sample File: By default, it will select the first file in the folder. This is usually fine since all your files are structured the same way.
2. Select the Object to Extract: On the left, you'll see a preview of what's inside the sample file. This is where a consistent sheet name pays off. Click on the sheet that contains your data (e.g., "Sheet1"). You should see a preview of its data on the right.
3. Click OK.

Power BI will now work its magic. In the background, it creates a parameterized function that opens a file, finds the specified worksheet, and promotes the first row to be column headers. It then runs this function on every single file it found in your folder and appends the results together. This happens incredibly fast, even with dozens of files.

Working in the Power Query Editor

Once Power Query finishes its automated steps, you'll find yourself looking at a single, beautiful table containing the data from all of your Excel files. You'll notice that Power BI added a new column on the far left called Source.Name. This column contains the original filename for each row of data, which is incredibly useful. You can use it to filter by month, region, or whatever your file naming convention represents.

Now is your chance to perform any final data cleaning steps:

• Check Data Types: Power Query is good at guessing data types, but you should always double-check. Does your date column have a calendar icon? Do your numeric columns have a "123" icon? If not, click the icon in the column header and select the correct type.
• Rename or Remove Columns: Don't need the Source.Name column? Right-click its header and choose Remove. Want to rename a column for clarity? Right-click and select Rename.
• Handle Errors or Nulls: Look for any cells with error or null values and decide how to handle them. You can use Power Query to replace errors or filter out empty rows if necessary.

Step 4: Close & Apply to Load the Data

Once you are satisfied with your combined and cleaned table in the Power Query Editor, it's time to load it into your report. In the top-left corner of the Power Query Editor window, click the Close & Apply button.

Power BI will close the editor, apply all the steps you configured, and load the final dataset into your Power BI data model. In the "Data" pane on the right-hand side of your main Power BI window, you will now see your query as a single table, ready to be used in visuals, charts, and dashboards.

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

The Magic of Refreshing: Effortless Updates

You’ve done all this work to set up a robust data pipeline, but here's the best part. From now on, your work is done. The next time you get a new monthly report, you don't need to repeat any of these steps.

All you have to do is:

1. Take your new Excel file (e.g., Sales_April.xlsx) and drop it into the same dedicated folder you pointed Power BI to.
2. Open your Power BI report. Go to the Home tab.
3. Click the Refresh button.

That's it. Power BI will automatically scan the folder, notice the new file, run all the same combination and transformation steps on it, and append its data to your existing dataset. All your dashboards and visuals will update instantly. This single-click action transforms your reporting process from a manual chore into a fully automated system.

Troubleshooting Common Issues

Sometimes, things don't go perfectly. Here are a few common issues and how to fix them:

• Error After Refresh: "Column 'XYZ' of the table wasn't found." This usually happens if a new file has a different column header than the sample file. Open all files and make sure the column names are truly identical. Typos like "Sale" vs. "Sales" are common culprits.
• Data Type Mismatch Errors: If a refresh fails due to a data type error, it means some text slipped into a number column in one of your files. You can troubleshoot this in Power Query by adding a "Filter" step to the Source.Name column to isolate the problematic file and investigate it directly.
• Unwanted Files Being Loaded: If temporary files (like those starting with ~) appear in your folder and cause errors, you can add a filtering step right after connecting to the folder. In Power Query, filter the "Extension" column to only include files that end in .xlsx or .xls.

Final Thoughts

By connecting to a folder instead of individual files, you can build an automated, scalable process for your routine reports in Power BI. You've learned how to properly prepare your files, use the Power Query Editor to combine them intelligently, and leverage the power of a one-click refresh to keep your dashboards effortlessly up-to-date.

This process of connecting, cleaning, and automating data workflows is a huge leap forward from manual spreadsheet work. In fact, cutting out the tedious manual data prep work is exactly why we built Graphed. We connect directly to all your key business platforms - like Salesforce, Shopify, Google Analytics, and Facebook Ads - and use conversational AI to generate dashboards for you in seconds. It allows your whole team to skip the manual setup and get answers from your live data just by asking questions in plain English.