By the end of this you'll have one function - call it fnCleanText - that takes any piece of text, trims it, collapses the double spaces, and title-cases the result. Three queries will call it instead of each carrying their own copy of the same five steps. When the cleaning rule changes, and it will, you edit one function and all three queries inherit the fix on the next refresh.
You need Excel for Microsoft 365 or Power BI Desktop - Power Query is the same engine in both, so the function behaves identically. The setup is the usual mess: three queries, Customers, Suppliers, and Contacts, each importing a name column from a different system, each arriving with stray spaces and inconsistent casing. No M experience required beyond the one bit of syntax I'll show you in Step 1.
Why one function beats three copies
The obvious approach is to build the cleanup once - trim, collapse spaces, proper-case - and paste those steps into the other two queries. It works, right up until the rule changes. Someone with a surname like "McDonald" turns up, Text.Proper mangles it, and now the fix lives in three places and you're hoping you remember the third query exists. A function breaks that pattern: you write the logic once, give it a name, and the queries hold a reference to it instead of a copy. Fix the function, every caller gets the fix. There is no third copy to forget.
Step 1: Build the function in a blank query
Create a new blank query. In Excel that's Data > Get Data > From Other Sources > Blank Query; in Power BI Desktop it's Home > Get Data > Blank Query. Open the Advanced Editor and replace what's there with this:
(input as text) as text =>
let
Trimmed = Text.Trim(input),
Collapsed = Text.Combine(List.Select(Text.Split(Trimmed, " "), each _ <> ""), " "),
TitleCased = Text.Proper(Collapsed)
in
TitleCasedRename the query fnCleanText - the function icon next to it in the Queries pane confirms Power Query read it as a function. Here is what each line does. The first line, (input as text) as text =>, is the whole reason this is a function: it declares what the thing takes (one text value) and what it gives back (one text value). The type annotations are not decoration - declare input as text and the function rejects a number at the door, instead of failing three steps deep where the error is harder to read. Trimmed strips the leading and trailing whitespace. Collapsed splits the text on spaces, drops the empty pieces that double spaces leave behind, and rejoins with a single space. TitleCased proper-cases the result. One value in, one clean value out.
Step 2: Call it from your first query
Open the Customers query. Its name column is Customer Name. Add a step - the quickest way is the fx button beside the formula bar, which inserts a custom step - and enter:
= Table.TransformColumns(Source, {{"Customer Name", fnCleanText, type text}})Replace Source with whatever your previous step is actually called. Table.TransformColumns walks down the named column and runs fnCleanText on every value, in place: same column, cleaned contents. The type text keeps the column typed as text afterward. The Customers query no longer contains the cleaning logic - it contains one call to it.
Notice the column name lives here, at the call site, not inside the function. That is deliberate, and it's the next step's whole point.
Step 3: Reuse it across the other queries
Suppliers and Contacts need the same treatment, but their columns are named differently - Supplier and Full Name. Each query gets one line.
In Suppliers:
= Table.TransformColumns(Source, {{"Supplier", fnCleanText, type text}})In Contacts:
= Table.TransformColumns(Source, {{"Full Name", fnCleanText, type text}})Three queries, three different column names, one function. Because the column name is supplied at the call site and not baked into fnCleanText, the same function cleans Customer Name, Supplier, and Full Name without modification. That is the payoff the copy-paste version can never give you: a single definition that doesn't care what the column is called.
Step 4: Change the rule once, fix it everywhere
Now the part that justifies the whole exercise. A row comes in reading "MCDONALD" and Text.Proper returns "Mcdonald", because Proper only capitalises the first letter of each word. The business wants "McDonald". You fix it in one place - the function:
(input as text) as text =>
let
Trimmed = Text.Trim(input),
Collapsed = Text.Combine(List.Select(Text.Split(Trimmed, " "), each _ <> ""), " "),
TitleCased = Text.Proper(Collapsed),
Exceptions = Text.Replace(TitleCased, "Mcdonald", "McDonald")
in
ExceptionsSave the function. The Customers, Suppliers, and Contacts queries don't change at all - you don't even open them. On the next refresh, all three apply the new rule, because all three were calling the same function. One edit, three corrected outputs, zero chance of the third query drifting because you forgot it existed.
Chaining Text.Replace stops scaling once you have more than a handful of exceptions - at that point you'd hold the exceptions in a small table and merge it in - but the shape is identical: one function, one place to maintain the rule.
Common mistakes
Hard-coding the column name inside the function.
It's tempting to bake "Name" into the function so the call site is shorter. Don't. The moment a query has a column called Supplier or Full Name, a function that only knows Name is useless to it. Keep the function operating on a value - (input as text) - and name the column at the call site in Table.TransformColumns. One generic function, any column, is the entire reason this is reusable.
Treating Text.Proper as a finished name-casing engine.
Text.Proper capitalises the first letter of every word and lowercases the rest. That turns "McDonald" into "Mcdonald", title-cases the "de" in "de Vries", and has its own opinions about hyphens. It's a fine default first pass - just know it isn't correct for every name, which is exactly why you want the rule in one editable function rather than copied across three queries where each correction has to be made three separate times.
Using Table.AddColumn when you mean Table.TransformColumns.
Reach for Table.AddColumn - or the Invoke Custom Function button, which does the same thing - and you get a second column sitting next to the original rather than a cleaned version of it. Worse, invoking a function row by row against a foldable source (a SQL database, a SharePoint list over OData) often breaks query folding and pulls the whole table into memory. Table.TransformColumns rewrites the column in place and stays friendlier to folding. Use AddColumn only when you genuinely want to keep the original alongside the cleaned copy.
Frequently Asked Questions
Does this work in Excel, or only Power BI Desktop?
Both. Power Query is the same engine in each, so a function written in Excel's Power Query can be pasted into Power BI Desktop and runs identically - it's the same M underneath.
How do I stop Text.Proper from breaking names like McDonald or de Vries?
Add an exceptions step inside the function after the Text.Proper line - a Text.Replace for a few cases, or a small lookup table merged in for many. Because every query calls the one function, you fix the casing once and all of them inherit it. That single point of correction is the reason to build the function at all.
Can one function clean several columns at once?
Yes. Table.TransformColumns takes a list, so {{"First Name", fnCleanText, type text}, {"Last Name", fnCleanText, type text}} cleans both in a single step, each pointing at the same function. The function doesn't change - you only list more columns.
Will calling a custom function slow my refresh down?
It can, if it breaks query folding. Against a foldable source a per-value transform in Table.TransformColumns usually behaves, but a per-row Table.AddColumn that invokes the function often forces the whole table into memory first. On a few thousand rows you won't notice; on a few million you will. Fold what you can at the source and save the function for what the source can't do.
Do I have to write the M by hand?
No. Right-click a query and choose Create Function, or use the Combine Files button on a folder, and Power Query writes one for you. To build your own deliberately you only need the (input) => body shape, and the body is the same M your normal query steps already produce.
What's the difference between this and a query parameter?
A parameter stores a value you want to swap - a file path, a date, a dev-or-prod switch. A function stores logic - the steps themselves. You'll often use them together, a parameter feeding the source path and a function doing the cleaning, but they answer different questions: "which input" versus "what to do with it."
Where to go from here
The concept underneath this build - why a custom function is really a query turned inside out, and where Power Query already writes them for you without telling you - is this week's Magic Monday on custom functions. For the argument that duplicating a query and tweaking the copy is a mistake you pay for later, Wednesday's Hot Take makes the case in full. And the value-shaped sibling of this logic-shaped tool, the parameter, is the Click Bait on switching dev and prod sources. Write the cleaning once, name it, and let every query ask it for the answer.