Power QueryExercises

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
    Typed

Result

NameOrderIDQty
orders-2026-09.csvS-12
orders-2026-09.csvS-25
orders-2026-10.csvO-13

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

  1. Filter the files: only CSVs, so the readme doesn't break the parse.
  2. Parse each file: the custom column runs Csv.Document and PromoteHeaders on every file's Content, so every file is read the same way.
  3. Expand: stacking all the parsed tables is an append; keeping Name records where each row came from.
  4. 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.
Functions in this lesson:Csv.DocumentTable.AddColumnTable.ExpandTableColumnTable.SelectRows

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.