Power QueryExercises

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
    Sample

Result

SKUOnHand
K-0112
R-024

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

Testing on one named file first is exactly what the generated Transform Sample File query lets you do.

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
    Typed

Result

NameSKUOnHand
leedsK-0112
leedsR-024
yorkK-017

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.

The 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 binary and as table.
Functions in this lesson:Csv.DocumentTable.SkipTable.PromoteHeadersTable.AddColumn

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Combining files quiz