Analistable

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

Queries generated by Combine Files
QueryPurpose
Sample FileThe file used as the example (the first by default)
Parameter1Points the function at a file
Transform Sample FileYour steps — edit this to change every file
Transform FileThe function applied to each file (don't edit directly)
Your folder queryLists 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

  1. Put the files in one folder. Subfolders are included too.
  2. Data → Get Data → From File → From Folder, choose the folder.
  3. Click Combine → Combine & Transform Data.
  4. Pick the sheet or table (Excel) or check delimiter and encoding (CSV) in the Combine Files dialog. Click OK.
  5. 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 ~$.
  6. 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 like sales_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

Common errors
ErrorCauseFix
The key didn't match any rows in the tableA file lacks the sheet name used in the sampleUse Source{0}[Data] or rename the sheet
The column 'X' of the table wasn't foundA Changed Type step references a column missing in a fileRemove the column from that step or add MissingField.Ignore
DataFormat.ErrorA value can't convert to the column typeReplace errors, or change type later
Duplicated rowsThe output file is saved in the same folderSave 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:

Files in C:\Reports\Sales
FileRowsTotal Amount
sales_2026-07.xlsx41052300
sales_2026-08.xlsx38849850
sales_2026-09.xlsx40251120

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

Where to find From Folder
ProductMenu 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 DesktopHome → 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

  1. Save the new export into the same folder with the same naming pattern.
  2. Open the workbook and choose Data → Refresh All (or right-click the result table → Refresh).
  3. Check the Source.Name values in a PivotTable or the filter drop-down to confirm the new file was picked up.
  4. 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.

Related guides