Combine files from a folder
Apply one parsing step to every file in a folder and stack the results.
~12 min read · intermediate · Module 15: Combining files and parameters
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Import CSV files: delimiters, encodings and headers, Append tables and align their columns
Monthly exports pile up as separate files. Instead of importing each one, point Power Query at the folder. The folder source is a table of files: one row per file, with its binary Content, Name, Extension, Date modified and Folder Path.
One transformation for every file
Input folder OrdersFolder (3 files)
orders-2026-09.csv
OrderID,Qty
S-1,2
S-2,5
orders-2026-10.csv
OrderID,Qty
O-1,3
readme.txt
Exports land here nightly.
let
Source = OrdersFolder,
CsvOnly = Table.SelectRows(Source, each [Extension] = ".csv"),
Files = Table.SelectColumns(CsvOnly, {"Name", "Content"}),
Parsed = Table.AddColumn(Files, "Data", each Table.PromoteHeaders(Csv.Document([Content]))),
Expanded = Table.ExpandTableColumn(Table.RemoveColumns(Parsed, {"Content"}), "Data", {"OrderID", "Qty"}),
Typed = Table.TransformColumnTypes(Expanded, {{"Qty", Int64.Type}})
in
TypedResult
| Name | OrderID | Qty |
|---|---|---|
| orders-2026-09.csv | S-1 | 2 |
| orders-2026-09.csv | S-2 | 5 |
| orders-2026-10.csv | O-1 | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
- Filter the files: only CSVs, so the readme doesn't break the parse.
- Parse each file: the custom column runs Csv.Document and PromoteHeaders on every file's Content, so every file is read the same way.
- Expand: stacking all the parsed tables is an append; keeping Name records where each row came from.
- Type once, on the combined table.
What the Combine Files button does
In Excel and Power BI, Combine Files generates a helper function, a Sample File query and a Transform Sample File query. It's the same idea with more scaffolding: you edit the sample query, and every file gets the change because the helper function wraps it. The sample-file function lesson builds that structure by hand.
Refresh picks up new files
Next month's file lands in the folder and appears as another row of the folder table. No step mentions a file name, so it's parsed and appended with no edit. That only holds while every file has the same layout; see files whose columns differ.
Where to find this in Excel
- Excel for Windows
- Data > Get Data > Folder, then Combine & Transform Data
- What you see
- Power Query lists the folder's files, opens the Combine Files dialog with an example file, and builds helper queries for you.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Folder connector (Microsoft Learn); Combine files overview (Microsoft Learn).
Common mistakes
- Not filtering the folder, so a stray text or lock file breaks the parse.
- Dropping the Name column and losing track of which file a row came from.
- Typing columns inside each file's parse and again afterwards, so two places need maintaining.
Excel, Power BI and the simulator
- Excel: Data → Get Data → From File → From Folder. Power BI Desktop: Get data → Folder. For SharePoint or OneDrive for Business, use the SharePoint folder connector (SharePoint.Files).
- The Combine Files dialog asks you to pick a sample file; choose a typical one, not the oldest.
- The simulator uses fixture folders with the same columns as Folder.Files; Folder.Files and SharePoint.Files themselves raise UnsupportedFeature because the simulator never reads your disk.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Combine every monthly CSV in a folder (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- intermediate~12 minPro
Combine every monthly CSV in a folder (kite shop)
Read every file in the folder, keep the file name, and stack the orders into one typed table.
- intermediate~12 minPro
Combine every monthly CSV in a folder (bakery)
Read every file in the folder, keep the file name, and stack the orders into one typed table.
- intermediate~12 minPro
Combine every monthly CSV in a folder (bike rental)
Read every file in the folder, keep the file name, and stack the orders into one typed table.
- intermediate~10 minPro
Combine only the CSV files in a mixed folder (kites)
The folder also holds a README and a notes file. Combine just the .csv files, keeping the file name.
- intermediate~10 minPro
Combine only the CSV files in a mixed folder (bakery)
The folder also holds a README and a notes file. Combine just the .csv files, keeping the file name.
- advanced~12 minPro
Combine files that each start with banner lines (kites)
Every export starts with two banner lines before the header. Write one transform for a file and apply it to all of them.
- advanced~12 minPro
Combine files that each start with banner lines (bakery)
Every export starts with two banner lines before the header. Write one transform for a file and apply it to all of them.
- advanced~11 minPro
Combine files whose columns come in different orders (kites)
Different branches export the same three columns in different orders. Combine them by column name.
- advanced~11 minPro
Combine files whose columns come in different orders (bakery)
Different branches export the same three columns in different orders. Combine them by column name.
- intermediate~10 minPro
Take each file's month from its name (kites)
The files don't contain a date column, but their names do (orders-2026-03.csv). Add a Month date to every row.
- intermediate~10 minPro
Take each file's month from its name (bakery)
The files don't contain a date column, but their names do (orders-2026-03.csv). Add a Month date to every row.
- advanced~12 minPro
Combine files when newer ones gain a column (kites)
From March the export added a Discount column. Combine every file and keep Discount, null for older files.
- advanced~12 minPro
Combine files when newer ones gain a column (bakery)
From March the export added a Discount column. Combine every file and keep Discount, null for older files.
- intermediate~9 minPro
Load only the most recently saved file (kites)
Each month a new snapshot lands in the folder; only the newest one matters. Load it, whatever it's called.
- intermediate~9 minPro
Load only the most recently saved file (bakery)
Each month a new snapshot lands in the folder; only the newest one matters. Load it, whatever it's called.
- intermediate~10 minPro
Combine a folder of JSON files (marketplace)
A partner drops one JSON file per day, each a list of order records. Combine them all with the file name.
- intermediate~10 minPro
Combine a folder of JSON files (wholesale)
A partner drops one JSON file per day, each a list of order records. Combine them all with the file name.