Turn a sample-file transformation into a function
Build and test a reusable file-parsing function, as Combine Files does.
~11 min read · advanced · Module 15: Combining files and parameters
By Tahir Nas · Examples run in our simulator · Updated
Before this: Combine files from a folder, Write and use custom functions
When each file needs several steps (skip a banner, use a semicolon delimiter, promote headers, rename a column) the custom column gets crowded. Move those steps into a function that takes one file and returns one clean table. You can then test it on a single file and apply it to all of them.
Write and test the function
Input folder Exports (2 files)
leeds.csv
Stock export Leeds
SKU;OnHand
K-01;12
R-02;4
york.csv
Stock export York
SKU;OnHand
K-01;7
let
TransformFile = (file as binary) as table =>
let
Raw = Csv.Document(file, [Delimiter = ";"]),
NoBanner = Table.Skip(Raw, 1),
Promoted = Table.PromoteHeaders(NoBanner)
in
Promoted,
Sample = TransformFile(Exports{[Name = "leeds.csv"]}[Content])
in
SampleResult
| SKU | OnHand |
|---|---|
| K-01 | 12 |
| R-02 | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Apply it to every file
Input folder Exports (2 files)
leeds.csv
Stock export Leeds
SKU;OnHand
K-01;12
R-02;4
york.csv
Stock export York
SKU;OnHand
K-01;7
let
TransformFile = (file as binary) as table =>
let
Raw = Csv.Document(file, [Delimiter = ";"]),
NoBanner = Table.Skip(Raw, 1),
Promoted = Table.PromoteHeaders(NoBanner)
in
Promoted,
Files = Table.SelectColumns(Exports, {"Name", "Content"}),
Parsed = Table.AddColumn(Files, "Data", each TransformFile([Content])),
Expanded = Table.ExpandTableColumn(Table.RemoveColumns(Parsed, {"Content"}), "Data", {"SKU", "OnHand"}),
Store = Table.TransformColumns(Expanded, {{"Name", each Text.BeforeDelimiter(_, "."), type text}}),
Typed = Table.TransformColumnTypes(Store, {{"OnHand", Int64.Type}})
in
TypedResult
| Name | SKU | OnHand |
|---|---|---|
| leeds | K-01 | 12 |
| leeds | R-02 | 4 |
| york | K-01 | 7 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The file name becomes a Store value by dropping the extension. That kind of tidy-up belongs after the expand, where it's one step for all files.
Why a function
- One definition: change the delimiter once and every file follows.
- Testable: run it on the file that breaks, without touching the others.
- Typed parameters fail early: passing text instead of a file raises a clear error.
- It mirrors the generated Combine Files structure, so you can read and edit what the button creates.
let
TransformFile = (file as binary) as table => Table.PromoteHeaders(Csv.Document(file))
in
TransformFile("SKU,OnHand")Result: an error
Expression.Error: We cannot convert the value Text to type binary.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
as binary parameter type rejects a text value straight away, instead of failing somewhere inside.Where to find this in Excel
- Excel for Windows
- Queries pane > Helper queries > Transform Sample File
- What you see
- Combine Files creates this example query and a linked function; changes to the example query flow to every file.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Combine files overview (Microsoft Learn).
Common mistakes
- Editing the generated helper function directly instead of the Transform Sample File query, and losing the change when the function is regenerated.
- Testing only on the first file when a different file is the one that breaks.
- Putting combined-table steps (typing, renaming the Name column) inside the per-file function.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Combine Files creates Sample File, Parameter1, Transform Sample File and Transform File in a helper group. Edit Transform Sample File; Transform File follows it.
- To build it by hand: write the steps on one file, then right-click the query → Create Function.
- The simulator supports typed function parameters such as
as binaryandas table.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Combining files quiz