Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 56 ยท Power Query

Custom Functions and Reusable Queries

Write parameterised Power Query functions, invoke them on a column, choose between reference and duplicate, and organise staging queries.

Sooner or later you clean the same kind of data twice: twelve monthly files with the same layout, ten sheets that all need the same header fix, or a text column that must be tidied in three different queries. Copying the steps each time works until the rule changes, and then you must find and fix every copy. Power Query offers two tools for reuse: custom functions, which package steps behind parameters, and reusable queries, which let several queries build on one prepared table.

This lesson explains how to write a function, how to call it for every row, and when to choose Reference or Duplicate. It assumes you know the let structure and each from M Language Basics.

Power Query itself cannot run on this website. The M code below is read-only. The runnable examples are Python stand-ins: ordinary Python functions that behave like the M functions, and a small model of a staging query. They illustrate the idea; they are not an M engine.

A function is a query with parameters

Any query can become a function. The M syntax is a parameter list, then =>, then the body. A function that cleans one text value might look like this:

TEXT
(input as text) as text =>
let
    Trimmed = Text.Trim(input),
    Cleaned = Text.Clean(Trimmed),
    Proper = Text.Proper(Cleaned)
in
    Proper

If you name this query fnCleanName (a common prefix for functions), you can call it from any other query as fnCleanName(" aDA lovelace "). Some notes on the syntax:

  • Types on parameters (as text) are optional but helpful: they document the input and give a clear error when the wrong thing arrives.
  • A parameter marked optional can be left out. Inside the function it is null if missing: (x as number, optional rate as nullable number) => x * (1 + (rate ?? 0)).
  • The body can be a single expression, or a let ... in block when you need several steps.

When a query is a function, the Queries pane shows an fx icon, and selecting it shows an Invoke box where you can type test values.

Making a function from existing steps

You do not have to write functions from scratch. A very common route is:

  1. Build and test the steps against one sample (one file, one sheet, one value).
  2. Right-click the query and choose Create Function. Power Query asks for a name and turns the query's inputs into parameters.
  3. Call the new function from another query.

Another route is to convert a query that depends on a parameter (see Data Source Settings, Privacy Levels and Parameters) into a function by editing the first line of the Advanced Editor from let into a parameter list followed by => and let.

Calling a function for every row

Two features cover almost every use of a function:

  • Add Column, Invoke Custom Function calls the function once per row, taking its arguments from columns.
  • Transform on a column uses Table.TransformColumns with the function.
TEXT
let
    Source = Excel.CurrentWorkbook(){[Name = "Customers"]}[Content],
    Cleaned = Table.TransformColumns(Source, {{"Name", fnCleanName, type text}}),
    WithTax = Table.AddColumn(Cleaned, "Gross", each fnAddTax([Net], 0.2), type number)
in
    WithTax

The first call rewrites the Name column in place. The second adds a new column named Gross, where each supplies the current row, so [Net] is that row's net amount.

Three ways to reuse work

Function Same steps, different input.
Reference A new query that starts from another.
Duplicate An independent copy of all steps.

Pick by asking what should change when the source rule changes.

1 / 2

Reference versus Duplicate

Right-click a query and you will find both Duplicate and Reference. They look similar and behave very differently.

  • Duplicate copies every step into a new, independent query. Editing the original later does not change the copy. It is the right choice when you want to start from a snapshot and then diverge.
  • Reference creates a new query whose first step simply points at the original, like Source = Sales. When the original changes, the reference follows. It is the right choice when several outputs should share the same prepared base.

The usual pattern with Reference is a staging query: one query does the heavy cleaning and has Enable load switched off, so it does not become a table in the model. Other queries reference it and shape it into the tables you actually want.

flowchart LR
    A["Raw files"] --> B["Staging_Sales<br/>load off"]
    B --> C["FactSales<br/>load on"]
    B --> D["SalesByCustomer<br/>load on"]
    B --> E["Returns<br/>load on"]

One cleaned staging query feeds three loaded tables; change the cleaning once and all three follow.

You switch loading off by right-clicking the query and clearing Enable load. The query still runs when other queries need it, but its result is not stored in the model. A typical file layout uses a prefix, for example Staging_ or src_, and a folder in the Queries pane, so you can see at a glance which queries are building blocks.

References can slow a refresh if the engine cannot reuse the base work. Each referencing query may evaluate the staging query again unless the shared work can be pushed to the source (see Query Folding, Incremental Refresh and Query Performance). Keep staging queries lean, and use a dataflow or a database view when the base is very expensive.

Stand-in 1: a function applied to a column and per row

The first stand-in defines Python versions of the two functions above, fnCleanName and fnAddTax, and applies them like Transform and Add Column. The cleaning rule is written once; changing it changes every result.

Python Stand-in: fnCleanName and fnAddTax

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the real tool the functions are M and the table operations are Table.TransformColumns and Table.AddColumn. The Python code mirrors the structure, so you can see how one definition serves many rows.

Applying one function to many files

The classic use of a custom function is combining files whose layout is the same. The Folder connector lists the files; a function turns one file's content into a table; then you call it for every row of the list and expand the result.

TEXT
let
    Files = Folder.Files("C:\data\monthly"),
    Parsed = Table.AddColumn(Files, "Data", each fnReadMonth([Content])),
    Combined = Table.Combine(Parsed[Data])
in
    Combined

The Combine Files button in the editor builds exactly this structure for you: a sample query, a function made from it, and a final query that invokes the function per file. If a later file changes layout, you fix the function once, and every file follows.

Stand-in 2: staging query, reference and duplicate

The second stand-in models the staging pattern. A function returns a table; the "reference" queries call it, and a "duplicate" is a frozen copy. After the upstream rule changes you can see which outputs follow.

Python Stand-in: reference follows, duplicate does not

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The referenced queries follow the new rule, while the duplicate stays as it was. That is the practical difference when you choose between the two commands.

Practical tips

  • Give function queries a clear prefix such as fn, and add a description in the query properties; the Invoke box then doubles as a test bench.
  • Test a function against one input before you apply it to a whole table. A wrong function is much easier to debug on a single row.
  • Keep functions small. A function that does one thing (clean a name, parse a date format) is reusable; one that does everything is not.
  • Prefer Reference over Duplicate unless you really want the copy to diverge. Many duplicated queries are the Power Query version of copy-and-paste code.
  • Turn off Enable load for staging queries and for helper tables you only use inside Power Query.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which M text defines a function with one parameter?

    M functions are written as a parameter list followed by => and a body. The let and each forms alone do not define a named parameter list in this way, and the last form is JavaScript.
  2. 2. What is the main difference between Reference and Duplicate?

    A referenced query starts from the other query's result, so changes flow through. A duplicate is an independent copy that you can edit separately.
  3. 3. Why switch off Enable load on a staging query?

    Disabling load keeps the query as a building block. It still runs when referenced, but it does not add a table to the model.
  4. 4. How does Add Column, Invoke Custom Function call your function?

    The invoke command creates a new column by calling the function for each row, mapping column values to the function's parameters.

Summary

  • A function is a query with a parameter list and =>; the fx icon marks it, and Invoke lets you test it.
  • Create Function turns tested steps into a function; Invoke Custom Function and Table.TransformColumns apply it to rows and columns.
  • Use Reference for shared bases and Duplicate only for independent copies.
  • A staging query with Enable load off builds a clean base that several loaded queries share.
  • Combining files in a folder is one function plus a call per file.
  • The Python stand-ins mirror the structure; the real functions are M.

Next, learn what makes queries fast or slow in Query Folding, Incremental Refresh and Query Performance.