---
title: "Power Query from Scratch, Part 5: Parameters, functions and a model that refreshes itself"
date: 2026-07-30T00:00:00Z
updated: 2026-07-26T20:32:11Z
tags: ["Power Query", "Excel", "Data Transformation", "M Language", "Getting Started", "Parameters", "Custom Function"]
canonical: https://bianca.codes/blog/power-query-from-scratch-part-5-parameters-functions-and-a-model-that-refreshes-itself/
---

# Power Query from Scratch, Part 5: Parameters, functions and a model that refreshes itself

_A hardcoded folder path and a copy-pasted business rule are the same problem, and parameters plus functions are how you stop editing M code every month._

← Previously: [Power Query from Scratch, Part 4: Custom columns and your first look at M](/blog/power-query-from-scratch-part-4-custom-columns-and-your-first-look-at-m/) (23 Jul)

**What this post covers**: turning a value that's going to change every month, like a folder path or a chase threshold, into a parameter instead of a line buried in the M code; turning a query into a function so the logic behind it lives in one place instead of copy-pasted into five; and what "the model refreshes itself" actually buys you once both of those are done.

## The scenario: the folder that moves every January

Part 2 built a query against a folder of regional sales files:

```fsharp
let
    Source = Folder.Files("C:\Reports\Regional-Sales"),
    FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")),
    Invoked = Table.AddColumn(FilteredFiles, "Transform", each #"Transform File"([Content])),
    Combined = Table.Combine(Invoked[Transform])
in
    Combined
```

It works, and it will keep working right up until IT reorganises the shared drive, or someone spins up a new year's folder because "2026" is apparently not a permanent naming scheme, or you move the whole thing to a different machine. The folder path is a string, typed once, buried inside a `let` block. Fixing it means opening Advanced Editor, finding the line, and editing it by hand. Fine for one query. Genuinely bad when the same path is hardcoded into three queries that all read from that folder, because now you're editing it three times and hoping you didn't miss one.

The Chase logic from Part 4 has the same problem in a different shape. `if [Balance] > 500 and [Days Overdue] > 30` is a business rule, not a fact about the universe, and finance will eventually change the threshold. When they do, you're back in the Advanced Editor, except this time the same logic might be pasted into two or three queries that each needed their own version of the flag.

Both problems are the same problem: a value or a piece of logic that lives inside the M code instead of somewhere you can change it without touching the code. Parameters fix the first. Functions fix the second.

## A parameter is a value you're allowed to change without opening the editor

Home tab, Manage Parameters, New. Name it `FolderPath`, set the type to Text, give it a current value of `C:\Reports\Regional-Sales`, and click OK. Power Query creates something that looks like a query in the sidebar but behaves like a named, typed constant everywhere else in the workbook.

Go back to the folder query and replace the literal string with the parameter's name:

```fsharp
let
    Source = Folder.Files(FolderPath),
    FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")),
    Invoked = Table.AddColumn(FilteredFiles, "Transform", each #"Transform File"([Content])),
    Combined = Table.Combine(Invoked[Transform])
in
    Combined
```

One line changed. Everything else is identical, which is worth noticing: `FolderPath` is just an identifier, the same way `#"Changed Type"` is an identifier for the output of a previous step. Power Query doesn't care whether a name refers to a step, a parameter, or another query. If any query needs that folder, point it at `FolderPath` instead of typing the string again.

Next January, the folder moves. Open Manage Parameters, change the current value, hit Refresh All. Every query built on `FolderPath` picks up the new location without anyone touching the Advanced Editor. That's the whole feature. It's not smarter than editing the string by hand, it's just editable from one place instead of every place it was pasted.

Parameters aren't limited to Text. Decimal Number, Whole Number, Date, and True/False are all options, and the dialog also lets you constrain a parameter to a fixed "List of values", turning it into a dropdown instead of free text, useful when the only sane options are a short, known list (a region code, a fiscal year) and you'd rather someone pick from four correct answers than type a fifth wrong one.

## Turning a query into a function

A parameter solves "this value changes." A function solves "this logic gets copy-pasted." The Chase flag from Part 4 is the second problem: right now it's one query's custom column, but it needs to run the same way against every region's file, and finance's thresholds are exactly the kind of number that gets revised mid-quarter.

The manual version, typed straight into a new blank query's Advanced Editor:

```fsharp
(Balance as nullable number, DaysOverdue as nullable number, MinBalance as number, MinDays as number) as text =>
if Balance <> null and DaysOverdue <> null
    and Balance > MinBalance and DaysOverdue > MinDays
then "Chase" else "Wait"
```

That's a function. The part before `=>` is the parameter list, each one typed the same way M types everything (`as nullable number` because a real Balance column will have nulls, `as number` for thresholds that are always supplied). The part after `=>` is the body, and it should look familiar: it's Part 4's null-guard logic, unchanged, just no longer welded to one specific query's column references.

You don't have to write it by hand. Right-click any existing query and choose Create Function. Power Query inspects the query's steps, works out which values look like they should be parameters rather than fixed values, and generates the wrapper for you, naming the detected inputs after the columns or literals it found. It's a reasonable first draft. Checking that it guessed the right inputs, and typing them properly (the generated version defaults everything to `as any`, which defeats the point), is still on you.

## Calling the function without retyping the logic

A function sitting in the query pane does nothing until something invokes it. Two ways to do that, and they cover almost every real case.

From the ribbon: Add Column \> Invoke Custom Function. Pick the function, and Power Query shows you its parameter list with a dropdown next to each one, letting you map a column (Balance, Days Overdue) or a fixed value (a parameter, or a literal) to each argument. This is the UI path, and it writes the formula for you.

Written by hand, invoking the function above from a query called `Source` looks like:

```fsharp
= Table.AddColumn(Source, "Chase", each fnFlagOverdue([Balance], [Days Overdue], BalanceThreshold, DaysThreshold))
```

Two parameters (`BalanceThreshold`, `DaysThreshold`) feeding straight into the function, alongside two column references. Change the parameters' current values when finance revises the policy, refresh, and every query that calls `fnFlagOverdue` re-flags every row with the new thresholds. Change the function's logic instead, and every query calling it changes too, in one edit, instead of finding every copy-pasted `if` statement across the workbook and hoping you found all of them.

## What "refreshes itself" actually means

Not "you never open Power Query again." A model like this still needs attention the moment the shape of the incoming data changes, the same schema-drift warning from Part 2: if next month's file renames a column, the Transform File function built from January's sample doesn't know that, parameter or no parameter. What parameters and functions actually buy you is narrower and more honest than "zero maintenance": the things you already knew were going to change, a folder location, a threshold, a fiscal year, become one value you edit in one place, instead of a string you have to remember you buried somewhere inside three different queries' M code.

That's the whole arc of this series, in one sentence. Part 1 said Power Query is a set of instructions that re-runs on refresh. Parts 2 through 4 were about writing good instructions. This part is about making the instructions themselves easy to steer, so refresh actually means "run again with this month's answer" instead of "run again and hope nothing you'd need to change by hand has changed."

## Frequently Asked Questions

### **What data types can a parameter be?**

Text, Decimal Number, Whole Number, Date, Date/Time, Date/Time/Timezone, Duration, True/False, and Any. You can also constrain any of them to a fixed "List of values", turning the parameter into a dropdown of pre-approved options instead of free entry.

### **Can I turn an existing query into a function without rewriting it from scratch?**

Yes. Right-click the query and choose Create Function. Power Query detects the values inside the query that look like they should be inputs and generates the wrapper automatically. Always re-check and properly type the detected parameters afterward; the generated defaults are usually `as any`, which works but throws away the type-checking a function is partly there to give you.

### **Can a function call another function?**

Yes, with no special syntax. A function's body is ordinary M, and a function reference behaves exactly like a query reference inside it. Nesting them is normal and often clearer than one long function trying to do everything.

### **Does invoking a function slow a query down?**

For a local file or workbook source, no meaningful difference. Against a database table, it can matter: Power Query normally tries to push steps back to the source as SQL, a behaviour called query folding, and a per-row custom function call is usually one of the things that breaks the fold. If you're calling a function against a live database table and refresh gets noticeably slower, that's the first thing to check.

### **What happens if I rename or delete a parameter a query depends on?**

The same cascade as any other broken reference: every step downstream that names it stops resolving and errors immediately, the same "steps are a chain" behaviour from Part 1. Rename deliberately and fix the references in the same pass, the same discipline as renaming an Applied Step.

### **Does this mean I never have to edit a query again?**

No, and that's worth being honest about. Parameters and functions cover the values and logic you already knew in advance would change. A genuinely new problem, a column that gets renamed, a file that arrives in a different shape, a transform nobody anticipated, still needs someone to open the query and fix it. What you've bought is fewer of those trips, not zero.

## The four parts that got you here

- [Part 1: The mental model nobody teaches you](/blog/power-query-from-scratch-part-1-the-mental-model-nobody-teaches-you/) (2 Jul) - Power Query is a step recorder, not a cleanup tool. Every click writes an M step that re-runs, from the top, on every refresh.
- [Part 2: Importing and combining without the copy-paste](/blog/power-query-from-scratch-part-2-importing-and-combining-without-the-copy-paste/) (9 Jul) - pulling from files, folders, and multiple sheets, and the append-versus-merge decision that trips up almost everyone.
- [Part 3: The transforms that actually matter](/blog/power-query-from-scratch-part-3-the-transforms-that-actually-matter/) (16 Jul) - unpivot, split column, and group by: the three transforms that solve most of what makes real-world data messy.
- [Part 4: Custom columns and your first look at M](/blog/power-query-from-scratch-part-4-custom-columns-and-your-first-look-at-m/) (23 Jul) - the moment the UI runs out, your first line of M, and the three ways a custom column breaks: a capital letter, a missing else, and null.

_That's the whole series, start to finish._