How to Load Multiple Files in Power BI
Manually importing individual data files into Power BI every week or month is a classic time-waster. If you’re exporting monthly sales reports or weekly marketing data, you know the drill: import April’s data, import May’s data, import June’s data, and so on. This article will show you a much faster way to load multiple files from a single folder at once, creating a refreshable query that automatically pulls in new files as you add them.
Before You Start: Setting Your Files Up for Success
Before jumping into Power BI, a little preparation goes a long way. For the “Get Data from Folder” feature to work its magic smoothly, your files need to be consistent. Think of it like stacking blocks - they need to be the same shape to build a stable tower.
Here are the ground rules for your source files:
- All files must live in one folder. Create a dedicated folder for your reports (e.g., "Monthly Sales CSVs") and move all the files you want to combine into it. Don’t mix in other unrelated documents.
- Keep the file type the same. This method works best if all your files are the same type - all
.csv, all.xlsx, or all.txt, for example. While Power Query can filter by file type, it's far simpler to start with a clean, organized folder. - The data structure must be identical. This is the most important rule. Each file must have the exact same column headers, in the same order. "Customer Name" in one file and "Client Name" in another will cause errors. Likewise, the number of columns should be consistent across all files.
Taking a few minutes to clean up and standardize your source files now will save you a massive headache later. Once your folder is tidy and your files are structured consistently, you're ready to go.
Step-by-Step: Connecting to Your Folder in Power BI
With your files properly organized, it’s time to tell Power BI where to find them. This process establishes a connection to the folder itself, rather than to any individual file.
Step 1: Open the "Get Data" Menu
Open a new or existing Power BI Desktop report. In the Home tab of the ribbon, click on Get Data. To see all the available options, select More... at the bottom of the dropdown list.
Step 2: Select the "Folder" Connector
In the "Get Data" window, you'll see a list of categories on the left. Select File. From the options on the right, choose Folder and then click Connect.
Step 3: Provide Your Folder Path
Power BI will now ask for the location of your folder. You can either click Browse... to navigate to the folder through your file system or simply copy the folder path from your file explorer and paste it directly into the text box. Once the path is entered, click OK.
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.
Step 4: Combine & Transform Data
After you click OK, Power BI will show you a preview window. This window doesn't show the contents of your files just yet. Instead, it displays a list of metadata about the files found in that folder, such as the file name, extension, date created, and folder path.
At the bottom of this window, you have a few options. While you could click "Load" or "Transform Data," the correct choice here is Combine & Transform Data. This option tells Power BI that you don't just want a list of files, you want to open each file and stack its contents into one large, unified table.
Clicking this button will launch the Power Query Editor, which is where the real data combination happens.
Using Power Query to Finalize Your Import
Power Query is the engine that drives data transformation in Power BI. When you chose "Combine & Transform," you told it to start an automated process for pulling together your files. Now, you just need to give it a little guidance.
Step 5: The "Combine Files" Dialog
As Power Query opens, a new "Combine Files" dialog box will appear. Power BI needs to understand the structure of the data inside your files, so it asks you to provide a "sample file." It will use this one file as a template to process all the others.
Here’s what to do in this window:
- On the left side, under "Sample File," you can leave it as the 'First File.' Power BI just needs one example, and since all your files are formatted identically, any file will do.
- In the main pane, Power BI will show you what it found inside that sample file. If you are using Excel files with multiple sheets, you would select the correct sheet name here (e.g., 'Sheet1'). For CSV files, you'll typically just select the file icon itself.
- A short preview of the data will appear on the right. If it looks correct, click OK.
Step 6: Review the Result in Power Query
After clicking OK, Power Query performs a series of automated actions in the background. When it’s done, you'll see a single table containing the data from all of your files. This is your combined dataset!
Notice a new column has been added on the far left: Source.Name. This column contains the name of the original file where that row of data came from. This is incredibly useful for troubleshooting or for creating visuals based on the source file, like sales broken down by month if your file names are "Sales_January.csv," "Sales_February.csv," and so on.
On the left-hand "Queries" pane, you'll also see that Power BI has created a group of "Helper Queries" automatically. These include the "Sample File" logic it used as a template. For the most part, you can ignore these and just focus on your main, combined query.
Tidying Up Your Combined Data
Your data is now in Power BI, but it might need a few final touches before it's ready for reporting. Power Query gives you all the tools you need for this final cleanup phase.
Step 7: Check and Adjust Data Types
Power Query does a decent job of guessing the data type for each column (e.g., text, whole number, date), but it’s not always perfect. Scan your columns and make sure each one has the correct type. You can see the data type icon (like "ABC" for text, "123" for whole number, or a calendar for date) in each column header. If one is incorrect, click the icon and select the right type from the list.
Step 8: Remove, Rename, or Transform Columns
Now is the time to shape your data. Here are a few common actions:
- Remove unnecessary columns: Do you have columns that you won't use in your report? Right-click the column header and select Remove.
- Extract data from the file name: The "Source.Name" column is your friend. You can use the "Split Column" or "Column From Examples" features to extract useful information like dates or department names from your file names.
- Clean up text: Use the "Trim" and "Clean" functions (found under the "Transform" tab) to remove extra spaces or non-printable characters from your text fields.
Step 9: Close & Apply
Once you’re happy with how your combined table looks, navigate to the Home tab in the Power Query Editor and click the Close & Apply button. This will close the editor and load your shiny new table into the Power BI data model. You can now start building visuals and reports with your complete, unified dataset.
What If Things Go Wrong? Common Issues and Fixes
Sometimes, the combine process hits a snag. Here are a few common problems and how to solve them.
Mismatched Column Names or Schemas
This is the most common error. If a file has a slightly different column header (e.g., "Sale Amount" instead of "Sales Amount") or a different number of columns, the query can fail or fill your combined table with null values.
Solution: Go back to your source folder and fix the inconsistent file(s). Standardize all column names and structures, save the files, and then refresh your query in Power BI.
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.
Adding New Files Later (And Why It's Awesome)
This isn't a problem but a feature! The best part about this method is its scalability. What happens when you get next month's sales data?
Solution: Simply drop the new file (e.g., "Sales_July.csv") into the same folder you originally connected to. The next time you open your Power BI report and click the Refresh button, Power Query will automatically run through the process, pick up the new file, and append its data to your existing table. No manual importing needed, ever again.
Filtering Out Unwanted Files
What if your folder contains other files you don't want to include, like a 'notes.txt' or an old temporary file?
Solution: Before you hit the "Combine" button in the file preview screen (Step 4), click "Transform Data" instead. In Power Query, you'll see the list of file metadata. Use the filter button on the "Extension" column to select only the file type you want (e.g., ".csv"). Once filtered, click the "Combine Files" button (it looks like two downward-facing arrows) on the "Content" column header to proceed with the combining process on just your filtered files.
Final Thoughts
Learning how to load multiple files from a folder in Power BI is a game-changer. It turns a manual, repetitive, and error-prone task into an automated, one-time setup that saves hours and ensures your data is always consistent and up-to-date. By building a dynamic query that grows with your data, you can spend less time wrangling data and more time finding insights.
Setting up refreshable data models is a huge step in automating your reporting. We built Graphed because we believe getting insights shouldn't require so much setup, whether it's in a BI tool or a spreadsheet. We wanted to make combining data from different sources even easier. You just connect your platforms like Google Analytics, Shopify, and Google Ads once, then ask questions in simple, natural language like, "Show me traffic, sales, and ad spend in a single dashboard for last month." We handle all the connections and combinations behind the scenes so you can get straight to the answers.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.