How to combine files from a folder with Power Query
By the Analistable team · Updated · 5 min read
Choose Data → Get Data → From File → From Folder (Power BI: Get data → Folder), select the folder and click Combine & Transform Data. Power Query uses the first file as a sample, records your steps in a Transform Sample File query, applies them to every file through a generated function, and stacks the results with a Source.Name column. Edit the sample query to change how every file is processed.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
What Power Query creates
| Query | Purpose |
|---|---|
| Sample File | The file used as the example (the first by default) |
| Parameter1 | Points the function at a file |
| Transform Sample File | Your steps — edit this to change every file |
| Transform File | The function applied to each file (don't edit directly) |
| Your folder query | Lists the files, calls the function, expands the results |
The key idea: changes made in Transform Sample File (promote headers, remove top rows, change types) apply to every file on refresh.
Step by step
- Put the files in one folder. Subfolders are included too.
- Data → Get Data → From File → From Folder, choose the folder.
- Click Combine → Combine & Transform Data.
- Pick the sheet or table (Excel) or check delimiter and encoding (CSV) in the Combine Files dialog. Click OK.
- In the main query, filter the Extension column (for example to
.xlsx) and the Name column to exclude files you don't want — such as temporary files starting with~$. - Fix column types in Transform Sample File, then Close & Load.
Useful changes
- Take the first sheet whatever its name: in Transform Sample File, change the navigation step to
Source{0}[Data]. - Keep every column from every file: replace the final expand step with
Table.Combine(PreviousStep[Transform File])— the default expand only keeps the sample file's columns. See combining files with different columns. - Parse dates from the file name: add a custom column on Source.Name, such as
Date.FromText(Text.Middle([Source.Name], 6, 10))for names likesales_2026-09-30.csv. - Files on SharePoint: use From SharePoint Folder with the site URL, then filter the Folder Path column.
Refresh errors and fixes
| Error | Cause | Fix |
|---|---|---|
| The key didn't match any rows in the table | A file lacks the sheet name used in the sample | Use Source{0}[Data] or rename the sheet |
| The column 'X' of the table wasn't found | A Changed Type step references a column missing in a file | Remove the column from that step or add MissingField.Ignore |
| DataFormat.Error | A value can't convert to the column type | Replace errors, or change type later |
| Duplicated rows | The output file is saved in the same folder | Save the result elsewhere or filter its name out |
Want the simple version first? See combining Excel files into one spreadsheet or, for CSV, combining CSV files in Excel.
Worked example: monthly sales files
A folder holds three exports, each with the columns Date, Region and Amount:
| File | Rows | Total Amount |
|---|---|---|
| sales_2026-07.xlsx | 410 | 52300 |
| sales_2026-08.xlsx | 388 | 49850 |
| sales_2026-09.xlsx | 402 | 51120 |
After Combine & Transform, the folder query should have 410 + 388 + 402 = 1,200 rows and a total of 52,300 + 49,850 + 51,120 = 153,270. Build a quick PivotTable on the result with Source.Name in Rows and Sum of Amount in Values: each file's line should match the figure in the table above. If one file is missing, it was filtered out or failed; if a total is too high, a file was counted twice.
Excel for Windows, Mac and Power BI
| Product | Menu path |
|---|---|
| Excel for Microsoft 365 / 2019 / 2021 (Windows) | Data → Get Data → From File → From Folder |
| Excel 2016 (Windows) | Data → New Query → From File → From Folder |
| Power BI Desktop | Home → Get data → More → Folder |
| Excel for Mac (Microsoft 365) | Data → Get Data (Power Query); folder support has arrived later than on Windows, so check your version |
If your Mac version doesn't offer a folder source, see combining Excel files on a Mac for alternatives.
Adding next month's file
- Save the new export into the same folder with the same naming pattern.
- Open the workbook and choose Data → Refresh All (or right-click the result table → Refresh).
- Check the Source.Name values in a PivotTable or the filter drop-down to confirm the new file was picked up.
- If the new file has a different sheet name or an extra column, fix it in Transform Sample File rather than in the main query.
Keep one folder per dataset. A mixed folder (sales and returns) needs filters on Name that are easy to forget, and every stray file becomes part of the result on the next refresh.
Common mistakes
- Editing the Transform File function directly: changes there are overwritten or break the link with the sample. Edit Transform Sample File instead.
- Leaving the hard-coded Changed Type step in the main query: it lists column names from the sample, so a renamed column in a new file errors on refresh. Remove that step and set types once at the end.
- Absolute paths when sharing: the folder path is stored in the Source step. Colleagues need the same path, or a parameter they can change under Manage Parameters.
- Hidden files and the output itself in the folder: filter the Attributes Hidden flag and the Name column.
For a browser alternative that doesn't need a folder or Power Query at all, the Excel combiner stacks files locally, and Power Query vs Analistable compares the two approaches.
Frequently asked questions
- How do I combine all files in a folder in Excel?
- Use Data → Get Data → From File → From Folder, then Combine & Transform Data. Every file in the folder is stacked into one table.
- How do I change the steps applied to every file?
- Edit the Transform Sample File query. Its steps are applied to each file through the generated function.
- Can Power Query combine files from SharePoint?
- Yes, with Get Data → From SharePoint Folder, filtered to the folder path you need.
- Why did my folder query double its rows?
- The output workbook or a copy of a file is saved inside the source folder, so it's combined too. Save the result elsewhere or filter its name out in the folder query.