Power QueryExercises

Combine monthly files into one table with Power Query

Guide · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice task

The manual process this replaces

Three monthly CSV files with 3, 2 and 4 rows are each transformed by the same steps and appended into one table of 9 rows with a Source.Name column. A new month's file is included on the next refresh.
Combine Files applies one set of steps to every file and appends the results.

Open each month's export, copy the rows, paste them under last month's, fix the headers, repeat. It takes minutes per file, and it's where duplicated or missing months come from. A folder query does the same in one refresh, the same way every time.

A complete query

The folder below has three monthly CSVs and an Excel lock file (~$…) left behind by someone who had a workbook open. The query keeps only CSVs whose names start with sales-:

Input folder SalesFolder (4 files)

sales-2026-08.csv

Date,Store,Amount
2026-08-03,Leeds,120.50
2026-08-17,York,80.00

sales-2026-09.csv

Date,Store,Amount
2026-09-02,Leeds,95.25

sales-2026-10.csv

Date,Store,Amount
2026-10-05,Bath,60.00
2026-10-21,Leeds,44.75

~$sales-2026-10.xlsx

let
    Source = SalesFolder,
    Wanted = Table.SelectRows(Source, each [Extension] = ".csv" and Text.StartsWith([Name], "sales-")),
    Parsed = Table.AddColumn(Wanted, "Data", each Table.PromoteHeaders(Csv.Document([Content]))),
    Combined = Table.ExpandTableColumn(Table.SelectColumns(Parsed, {"Name", "Data"}), "Data", {"Date", "Store", "Amount"}),
    Typed = Table.TransformColumnTypes(Combined, {{"Date", type date}, {"Amount", type number}}, "en-US"),
    Month = Table.TransformColumns(Typed, {{"Name", each Text.BetweenDelimiters(_, "sales-", ".csv"), type text}})
in
    Month

Result

NameDateStoreAmount
2026-082026-08-03Leeds120.5
2026-082026-08-17York80
2026-092026-09-02Leeds95.25
2026-102026-10-05Bath60
2026-102026-10-21Leeds44.75

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

The file name becomes a month label, so every row can be traced to its file.

Check every refresh

Two cheap checks catch most combine problems: how many files were read, and whether each month appears once. A missing or duplicated month shows up immediately:

Input folder SalesFolder (4 files)

sales-2026-08.csv

Date,Store,Amount
2026-08-03,Leeds,120.50
2026-08-17,York,80.00

sales-2026-09.csv

Date,Store,Amount
2026-09-02,Leeds,95.25

sales-2026-10.csv

Date,Store,Amount
2026-10-05,Bath,60.00
2026-10-21,Leeds,44.75

~$sales-2026-10.xlsx

let
    Wanted = Table.SelectRows(SalesFolder, each [Extension] = ".csv" and Text.StartsWith([Name], "sales-")),
    Parsed = Table.AddColumn(Wanted, "Rows", each Table.RowCount(Table.PromoteHeaders(Csv.Document([Content]))), Int64.Type)
in
    Table.SelectColumns(Parsed, {"Name", "Rows"})

Result

NameRows
sales-2026-08.csv2
sales-2026-09.csv1
sales-2026-10.csv2

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

When it breaks

  • A stray file (lock file, readme, a PDF): filter by extension and name pattern.
  • A file with an extra or renamed column: see combining files whose columns differ.
  • A file with a different delimiter or banner: move the parsing into a function and handle it there (sample-file functions).
  • A month appears twice: someone saved a copy (sales-2026-10 (1).csv). Tighten the name filter, and keep the per-file row count check.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.