You have built the same eight steps in four different queries. Trim the whitespace, fix the casing, strip the trailing apostrophe that one system insists on adding, split a column, rename it back. Each query carries its own copy. When the rule changes, and it always changes, you open all four and hope you remember the fourth one exists.
This is the Power Query version of pasting a formula into two hundred cells. The logic is correct and it is also scattered across the file with no single place to fix it. A custom function is the way out: you write the transformation once, give it a name, and every other query calls it instead of carrying its own copy.
The mental model
A custom function is a query turned inside out. A normal query is a fixed set of steps that runs on a fixed source and produces one result. A custom function is the same set of steps with the parts that change pulled out into parameters, so you can hand it different inputs and get the matching output each time.
That is the whole shift. Instead of "these steps, on this table," you have "these steps, on whatever you give me." The query stops being a finished thing and becomes a machine that other queries feed.
And you have almost certainly used one already without noticing. The next time you point Power Query at a folder and click Combine and Transform, look at what it builds in the Queries pane: a sample file, a query called Transform Sample File, and a function called Transform File. Power Query writes a custom function from your sample and maps it over every file in the folder. The feature you have been using to merge a hundred CSVs is a custom function with a button on top.
The syntax
In M, a function is a value, the same way a number or a table is a value. You write it as a parameter list, then =>, then the body:
(input) => input * 2That is a complete, working function. Assign it to a query named fnDouble and fnDouble(21) returns 42. The body can be a single expression like that, or a full let ... in block when the logic needs room:
(Input as text) as text =>
let
Trimmed = Text.Trim(Input),
Collapsed = Text.Combine(List.Select(Text.Split(Trimmed, " "), each _ <> ""), " "),
Cased = Text.Proper(Collapsed)
in
CasedSame shape every time: what it takes (Input as text), what it gives back (as text), and the steps in between. Trim the ends, drop the empty pieces left by double spaces, fix the casing. Assign that to a blank query and name it fnCleanName, and you have a reusable cleaner. The as text annotations are optional; leave them off and the function accepts anything, which is convenient right up until someone passes a number and the error lands three steps deep instead of at the door.
What this looks like on a real problem
Say three queries, Customers, Suppliers, and Contacts, all import a Name column from three different systems, and all three need the same cleanup: trim, collapse double spaces, fix the casing. The copy-paste approach builds those steps once and duplicates them into the other two. Three copies, three things to update, three chances for one to drift the day someone tweaks it in a hurry.
Instead, make the cleanup a function. Create a blank query (New Source, then Blank Query), open the Advanced Editor, and paste the fnCleanName definition from above. Now each of the three real queries gets one step:
= Table.TransformColumns(Source, {{"Name", fnCleanName, type text}})Each query calls fnCleanName on its own Name column. The Customers query no longer contains the cleaning logic. It contains a reference to the logic. When the casing rule changes, because someone with a surname like "McDonald" or "de Vries" turns up and Text.Proper cheerfully mangles it, you fix fnCleanName once and all three queries inherit the fix on the next refresh.
If you prefer the menu, the Add Column tab has an Invoke Custom Function button that does the same thing through a dialog and writes the same M behind it. Either way the result is one definition and a workbook full of calls to it, which is the entire point.
What it doesn't do
A custom function is not a parameter, even though both live in the Queries pane and both exist to stop you hard-coding things. A parameter holds a value: a file path, an environment name, a cutoff date. A function holds logic. You will often use them together, a parameter supplying the path and a function doing the work, but they are companions, not alternatives.
It will not always fold. When Power Query can push your steps down to the source, a SQL database or a SharePoint list over OData, it runs them at the source instead of dragging everything into memory first. Invoking a custom function row by row, the Table.AddColumn(..., each fnDoThing([col])) pattern, frequently breaks that and forces the whole table to load locally. On a few thousand rows you will not notice. On a few million you will, and you will have traded a maintainable file for a slow refresh. That is a real trade, not a free win.
And it is strict about its signature, which is a feature. Declare fnCleanName as taking one text argument, call it with a column of numbers, and it errors instead of guessing. That is the function stopping a silent wrong answer at the door, the opposite of the nested-mess approach where the wrong type propagates quietly until the output looks odd and nobody can say why.
Frequently Asked Questions
What is the difference between a custom function and a query parameter in Power Query?
A parameter stores a value you want to reuse or swap: a folder path, a date, a dev-or-prod switch. A custom function stores a transformation you want to reuse, the actual steps. Parameters answer "which input"; functions answer "what to do with it." They are different tools that work well together, and the Manage Parameters dialog has nothing to do with defining a function.
Do I need to know M to write a custom function?
Not to start. Power Query can generate one for you: right-click a query and choose Create Function, or use the Combine Files button, and it writes the M itself. To write your own deliberately you only need the (parameters) => body shape, and the body is the same M your normal query steps already produce. You are naming logic you can mostly build with the mouse.
Where is a custom function stored, and does it travel with the file?
It lives in the workbook (or the Power BI file) as a query, alongside your normal queries. Send the .xlsx or .pbix to someone else and the function goes with it. It is not tied to your machine or your installation the way an old VBA function in a personal macro workbook could be.
Can one custom function call another?
Yes. A function is a query, and any query can reference any other, so you can build small functions and compose them into larger ones. Keep the chain shallow enough to debug; a function that calls four others that each call three more is quick to write and slow to untangle when one of them returns an error.
Does this work in Excel, or only Power BI?
Both. Power Query is the same engine in Excel and Power BI Desktop, and custom functions behave identically in each. A function written in Excel's Power Query can be pasted into Power BI's and run the same way, because it is the same M underneath.
Why did my refresh get slower after I added a custom function?
Most likely you broke query folding. When the source is a database or an OData feed, Power Query tries to do the work at the source; calling a custom function per row often stops that and pulls the whole table into memory first. If the function's logic could have been done in the source step instead, do it there. Save custom functions for the transformations the source cannot fold anyway.
Where this fits
The companion to the function is the parameter, the same write-once instinct applied to values instead of logic, and that is the Jun 20 Click Bait on switching dev and prod sources. For the full build, where you write a reusable text-cleaning function and call it from three queries end to end, see the Jun 23 Build It. And for the argument about why duplicating a query and tweaking the copy is a mistake you pay for later, there is the Jun 24 Hot Take. Write the logic once, name it, and let every other query ask it for the answer.