Combine files from a folder in Power Query

This is the big one, the pattern that makes M pay for itself. Say you receive the same report every month as a separate file: 2024-01_Region.csv, 2024-02_Region.csv, and so on. You need them cleaned identically and stacked into one analysis table that grows by itself when next month's file arrives in the folder. By hand, that's a copy-paste chore every month. As a pipeline, it's drop the file in and refresh.

In the ribbon, go Data ▸ Get Data ▸ From File ▸ From Folder and point at the folder. Excel shows a preview table with one row per file and columns like Content, which holds the file's binary, plus Name, Extension, Date modified and Folder Path. From here the one-click route is Combine ▸ Combine & Transform Data, which builds the Transform Sample File function and the driver query for you. Your goal is to read that driver, and to hand-write it when you need to.

What does the driver query look like in M?

Here's the driver query written out in explicit M, using a per-file function called CleanStatement. Read it as five steps:

let
    Source   = Folder.Files("C:\Statements"),
    KeepCsv  = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")),
    Invoke   = Table.AddColumn(KeepCsv, "Data", each CleanStatement([Content]), type table),
    AddMonth = Table.AddColumn(Invoke, "Month", each Text.BeforeDelimiter([Name], "_"), type text),
    Expanded = Table.ExpandTableColumn(AddMonth, "Data", {"SKU", "Revenue"}),
    KeepCols = Table.SelectColumns(Expanded, {"Month", "SKU", "Revenue"})
in
    KeepCols

Take it a line at a time. Folder.Files returns the file list as a table. KeepCsv filters to the files you actually want. Never trust a folder to contain only what you expect, because a stray Thumbs.db or ~$ lock file will otherwise crash the pipeline. Invoke adds a column named Data in which every cell is the whole cleaned table for that file, produced by invoking CleanStatement on the file's [Content] binary. AddMonth derives a Month label from each file's [Name] with Text.BeforeDelimiter([Name], "_"), turning 2024-01_Region.csv into 2024-01. It does this before expanding, so the month rides along with every row of that file. Expanded unpacks each nested table into real rows with Table.ExpandTableColumn, and KeepCols trims to the final shape. What comes out is one tall table with Month, SKU, and Revenue, unioned across every file.

The per-file function it invokes is the "clean one file" template you met in custom functions in Power Query:

(statement as binary) as table =>
let
    Source   = Csv.Document(statement, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Trimmed  = Table.TransformColumns(Promoted, {{"SKU", Text.Trim, type text}}),
    Typed    = Table.TransformColumnTypes(Trimmed, {{"SKU", type text}, {"Revenue", Int64.Type}})
in
    Typed

It parses the CSV, promotes the header row, trims stray whitespace from SKU, and sets types. Because it's a function of one file, the driver can call it a hundred times and you never re-specify the cleaning. When you need total revenue, it's a single expression over the final list of revenues: List.Sum(KeepCols[Revenue]).

Why take the month from the file name?

Why bother taking the month from the file name? Monthly files usually don't carry their own month inside the data. The month is in the file name. Deriving it at ingestion, instead of typing it in or, worse, sorting it out later, is a small move. It makes the combined table describe itself, and it keeps the table correct as files pile up. You'll reuse this "metadata from the file name" pattern in almost every folder pipeline you ever build, pulling a date, a region, a book, an entity out of the name and attaching it as a column.