If you read part 1 in this series, you may have picked up a theme about expressions that produce values: A simple statement, like 1
, is an expression that produces a value. let is also an expression that produces a value.
Guess what? A function is an expression that ultimately produces a value. Unlike the expressions we looked at last time, a function only produces this value when it’s invoked.
Usually, parameters are passed to a function when it’s invoked. The function can reference these inputs as it computes what it will return.
Series Index
- Introduction, Simple Expressions &
let
(part 1) - Functions: Defining (part 2) [this post]
- Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
- Variables & Identifiers (part 4)
- Paradigm (part 5)
- Types—Intro & Text (Strings) (part 6)
- Types—Numbers (part 7)
- Types—The Temporal Family (part 8)
- Types—Logical, Null, Binary (part 9)
- Types—List, Record (part 10)
- Tables—Syntax (part 11)
- Tables—Table Think I (part 12)
- Tables—Table Think II (part 13)
- Control Structure (part 14)
- Error Handling (part 15)
- Type System I – Basics (part 16)
- Type System II – Facets (part 17)
- Type System III – Custom Types (part 18)
- Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
- Metadata (part 20)
- Identifier Scope & Sections (part 21)
- Identifier Scope II – Controlling the Global Environment, Closures (part 22)
- Query Folding I (part 23)
- Query Folding II (part 24)
- Extending the Global Environment (part 25)
- More to come!
Defining
A function’s definition starts with the list of expected parameters inside a pair of parenthesis, followed by =>
, followed by the function body expression (or function body, for short). This expression defines how to compute the value that’s returned when the function is invoked. Any parameters defined for the function become variables inside this expression which it can use when computing the return value.
The below function accepts two arguments and multiplies them together.
(x, y) => x * y
Since a function’s body is an expression that produces a value and a let expression is an expression that produces a value, let can be used to define the function’s body.
(a, b, x) =>
let
Result = x * (a + b)
in
Result
Technically, functions can be defined that take no parameters. This is rarely done in practice. Why use a function like this when a plain old variable would do?!
() => "Hello, world! This is a super-simple, almost pointless function"
Where Defined?
Nested
Just like other expressions, functions can be nested inside other expressions and other expressions can be nested inside functions. This means that functions can be nested inside functions, because functions are expressions!
let
Input1 = 10,
Input2 = 15,
Input3 = 30,
SecretFormula = (a, b, x) =>
let
Output = x * (a + b),
OutputDoubled = Output * 2
in
Output,
Result = SecretFormula(Input1 , 15, 30)
in
Result
=
Above we see a function defined inside a let
expression. Below, there’s a function with a function inside it.
(OrderAmount, DiscountPercentage) =>
let
NonDiscountedPercentage = (Rate) => 1 - Rate,
Result = OrderAmount * NonDiscountedPercentage(DiscountPercentage)
in
Result
Top-Level
In contrast to a nested function, a top-level function stands on its own. It can be referenced from other expressions (including other functions) but isn’t defined inside another expression.
In the query editor, creating a new blank query is the way to create any new top-level named expression, not simply queries. Think of “new blank query” as “new blank named expression.” The query editor will look at the expression you define, infer what it is (query, table, list, function, etc.) and automatically adjust accordingly. In the query editor, the name assigned to the new “query” (really: expression) is the name you’ll use to reference it from elsewhere in your project.
To define a stand-alone functions in the query editor:
- Create a new blank query.
- Choose advanced editor.
- Replace whatever is there with your function’s expression, starting with the parenthesized argument list.
- After exiting the advanced editor, rename the so-called query you just defined to something more appropriate.
- Now, you can reference this function by the name you just defined from elsewhere in your project.

Notice how the queries list shows your function with a function icon next to it. The query editor figured out that it’s really a function, not a query, and updated the icon appropriately.
Optional Parameters
By default, a value must be provided for each parameter when the function is invoked. If you’d like certain arguments to be optional, simply proceed them with optional
when defining the function’s parameter list.
(LastName, FirstName, optional MiddleName) =>
Text.Combine({ FirstName, MiddleName, LastName}, " ")
Invoking the above function invokes specifying at least two arguments (the required ones) and, optionally, a third. Both of the following are valid ways to invoke this function (assuming it’s named StringifyName):
StringifyName("Smith", "Joe")
StringifyName("Brown", "Robert", "James")
Optional arguments must be defined after the non-optional arguments. In other words, once you define an optional parameter, any parameters defined to the right of that parameter must also be marked optional
. To put it another way, an optional parameter can’t be followed by non-optional parameters.
Confusing Query Editor UI

Select the above function in a tool like Microsoft Excel or Power BI and—confusingly—the query editor shows all three arguments as optional, even though only one is optional according to the code.
Unfortunately, the query editor uses a different definition for optional than is used by the Power Query M language. To the query editor, optional means either the parameter is optional or it is required but can be null.
How does query editor handle the arguments it claims are optional if you leave them out?
In the editor, leave all three fields blank and click invoke. The expression generated will have the two “claimed optional” arguments set to null. For these arguments, the editor gave you the option of providing values. You didn’t, so it provided safe defaults for you. (Values had to be specified as these arguments really are required, despite what the editor’s UI claims.)
MyFunction(null, null) // older versions
MyFunction(null, null, null) // newer versions
As to the third argument, older query editor versions will omit it while newer versions will set it to null, as well (interesting!).
If you’re in the query editor and want to see which arguments are honestly and truly optional, skip over the Enter Parameters section and instead look at function signature. It’s displayed below the line at the very bottom. There, the real optional parameters are identified with an italicized optional.
Types
In the function signature, we also see the parameters’ types and the function’s return type. By default, they’re all type any
(notice the four as any
s in the signature).
You can be more specific about types when defining functions. This function specifies types for its parameters (both are number
) and return value (text
):
(PriceEach as number, Quantity as number) as text =>
"Total Cost: " & Number.ToText(PriceEach * Quantity)
Specifying types is not all or nothing: you can specify types for some arguments but not others. Whether or not you specify parameter types has no effect on whether or not you can specify the return type.
We’ll talk more about types in a later post.
Back in the UI
Not all types allow nulls. When a parameter is specified to be a non-nullable type, the query editor can’t default it to null and so won’t display (optional) by it unless the parameter was defined as optional in code.

To the right, the editor does not show (optional) by either parameter because both are non-nullable and non-optional parameters.
Again, to determine whether a parameter is truly required, skip the Enter Parameters section and look at the function’s signature.
Before we leave the topic of defining functions and the UI: There’s a way to provide documentation that the query editor will display when the function is selected. However, this requires using types and metadata, neither of which we’ve explored yet, so we’ll have to save this cool technique for later.
Next Time
Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3), including a look at Power Query M’s each.
Revision History
2020-10-09: Revised section “Confusion Query Editor UI” to reflect how newer editor versions will set omitted optional argument values to null in the function invocation code they generate.
Thanks for the blog series – really useful to understand the underlying functionality.
In your nested function example, there’s a small mistake using
DiscountRate
instead ofDiscountPercentage
inResult = ...
Thanks much for your kind words and for pointing out that error. Fixed!
Hello Ben,
Amazing post and thanks for the tutorial.
I have a Q for you related to the following code that you mentioned
In this function if I put
OrderAmount=10
andDiscountPercentage=20
, the invoked result is -190.From my understanding, it calculates by OrderAmount [10] x NonDiscountedPercentage[1-20].
Now you defined
NonDiscountedPercentage = (Rate) => 1 - Rate
and how 20 is identified by the code as “Rate”.I can reach the same result by using the following code
But in my code I am explicitly defining A and I fail to understand how does your code associate DiscountPercentage 20 as “Rate”.
Thank you in advance.
Samik
Hi Samik,
Happy New Year! Thanks for your question! You’re trying to apply a 20% discount, right? In M, percentages are typically represented as a number between 0 (0%) and 1 (100%). Using this scheme, 20% is represented as 0.2. If 10 is used as the amount and 0.2 is used as the discount, the total should come out to 8. Is that the result you see when you use 0.2?
Ben
Hi Ben, really clear explanation – I am enjoying reading your examples, even though the ideas are complex. Really wonderful blog – look forward to finishing up the series in Frankfurt airport before I take my next flight.
i’ve been struggling with M language since i’ve started in powerbi. this series of post is really inspirational, so i just wanted to say thank you!
Thank you for your tutorial! I would love to see a future post on function documentation and metadata. M code in general is really fun to use! –Christopher Hastings
Thanks for the suggestion, Christopher! A good idea. 🙂
Hi, Ben. I couldn’t understand your suggestion for “If you’re in the query editor and want to see which arguments are honestly and truly optional.”
I am using Power Query in Excel. I wonder if you base your tutorials in Power Query for Excel or Power BI. Although they are supposed to seem the same tool, I know there are differences between the two versions of PQ.
Thank you so much for all the content that you share.
Never mind, Ben!
I got it! One way to get to the Enter Parameters section and being able to see the function signature is (In Power Query for Excel): on the Queries pane right-click the query name and choose “Edit”
Thanks!
Hello Ben,
First of all, congratulations on this great blog.
I have replicated what is stated in section “Confusing Query Editor UI” and the response I get from the function is this:
1
MyFunction(null, null, null)
That is, the third parameter that is optional also takes it into account as null.
What is wrong?
Thank you!!
Interesting! How Query Editor handles this must have changed since I initially wrote the post. Thanks for letting me know about this.
As a follow up to this, you get proper optional/required UI behavior in Power BI if you specify a type for the parameter. It does make some sense to me that an untyped parameter is always optional, since null is available.
Hi, Ben.
Is there a way to have named parameters in functions and limit the parameter values to specific lists?
Like in PowerShell one can define parameters in a function as
and then call the function such as
func (Name = "xxx", Action = "Install")
Use case is having several optional parameters and calling only some of then w/o having to do fun
(param1, null, null, param4)
Thanks!
M doesn’t have a built-in concept of named parameters. If you wanted to achieve a similar effect, you could try defining the function to accept a single record as its argument, with the fields on this record used as the function’s parameters. When invoking the function, you’d pass it a record with just the fields you want to provide specified. In the function, you’d check the input record for each possible “parameter.” If it exists, you’d use its value; if not, you’d substitute a default value.
Great articles – enjoying reading them. Thanks for putting this togetehr
Hello Ben,
Very nice primer on M scripting. I have been hacking together M scripts to do some data manipulation for a project. I found a couple of lines of code that do a very nice job of creating a list of used countries from a column that has multiple sets of information using a master country list. (see lines with nrows and myList
I end up with exactly what I want in a single column. The problem is I don’t want a single column, I want the resultant column appended to the end of the rest of the data, resulting in
I am fairly sure this is simple, but I am at my wits end trying to figure it out. Than you very much
What if you switched to adding a new column to the table #”Renamed Columns” (using
Table.AddColumns
) then outputting that result?First off, probably one of the best articles on advanced m query functionality I’ve seen, and lets just say there are no blue links left in Google.
I implemented several custom parameter types with both dynamic and static allowed values. What I can’t seem to figure out is if there is a way to set one of the values as the default.
This works great for creating a dropdown selector, however this requires the user to select one of the values before they can execute the function. Do you if there is a way to have “true” selected by default?
Thanks for your kind words, Kevin.
I am not aware of a way to set a default in Query Editor’s UI like you describe. 🙁