Power Query M Primer (part 2):
Functions: Defining

November 28, 2017 •

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

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.

Query list showing just-renamed function

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

Query editor screen shot showing details/parameter prompt for function StringifyName

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 anys 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.

Query editor screen shot showing details/parameter prompt for function TotalCost

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.

19 thoughts on “Power Query M Primer (part 2):
Functions: Defining”

Andrew L February 2, 2018 at 1:21 am

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 of DiscountPercentage in Result = ...

Reply ↓
1. Ben Gribaudo Post authorFebruary 2, 2018 at 9:37 am
  
  Thanks much for your kind words and for pointing out that error. Fixed!
  
  Reply ↓
Samik Pal January 2, 2019 at 2:25 pm
Hello Ben,

Amazing post and thanks for the tutorial.
I have a Q for you related to the following code that you mentioned
```
(OrderAmount, DiscountPercentage) =>
let
  NonDiscountedPercentage = (Rate) => 1 - Rate,
  Result = OrderAmount * NonDiscountedPercentage(DiscountPercentage)
in
  Result
```
In this function if I put OrderAmount=10 and DiscountPercentage=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
```
let
    Source = (OrderAmount, DiscountPercentage)=>
             let
                A=(B)=>1-DiscountPercentage,
                Result=OrderAmount*A(DiscountPercentage)
             in
               Result              
in
    Source
```
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
Reply ↓
1. Ben Gribaudo Post authorJanuary 3, 2019 at 6:44 am
  
  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
  
  Reply ↓
Siraj February 19, 2019 at 2:29 am

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.

Reply ↓
Bruno February 22, 2019 at 11:38 am

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!

Reply ↓
Christopher Hastings May 14, 2019 at 4:36 pm

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

Reply ↓
1. Ben Gribaudo Post authorMay 30, 2019 at 5:55 pm
  
  Thanks for the suggestion, Christopher! A good idea. 🙂
  
  Reply ↓
Celia Alves June 6, 2020 at 7:47 am

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.

Reply ↓
Celia Alves June 6, 2020 at 7:57 am

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!

Reply ↓
Isidre Bagué August 12, 2020 at 9:24 am

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!!

Reply ↓
1. Ben Gribaudo Post authorAugust 14, 2020 at 10:50 am
  
  Interesting! How Query Editor handles this must have changed since I initially wrote the post. Thanks for letting me know about this.
  
  Reply ↓
A April 9, 2021 at 7:57 pm
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
```
param (
	[Parameter(Mandatory=$true)]
        [string]$Name,

	[Parameter(Mandatory=$true)]
	[ValidateSet("Install", "Uninstall", "Detect")]
	[string]$Action
)
```
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!
Reply ↓
1. Ben Gribaudo Post authorApril 14, 2021 at 8:08 am
  
  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.
  
  Reply ↓
Durga April 18, 2021 at 7:38 pm

Great articles – enjoying reading them. Thanks for putting this togetehr

Reply ↓

Aaron Trisler October 6, 2021 at 11:17 pm

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

   Source = Excel.Workbook(blah blah blah)
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hrs", Int64.Type}, {"Country", type text}, {"Categories", type text}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Blank Rows",{"Country"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Categories", "Categories - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Categories - Copy", "FoundCountrys"}}),
   nrows=Table.RowCount(myCountry),
   myList= Table.FromColumns(List.Zip(List.Transform({0..nrows-1},each Text.Split(myCountry[CountryList]{_},","))))
in
   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

Hrs   Categories    myList

I am fairly sure this is simple, but I am at my wits end trying to figure it out. Than you very much

Reply ↓

Ben Gribaudo Post authorOctober 7, 2021 at 10:05 am

What if you switched to adding a new column to the table #”Renamed Columns” (using Table.AddColumns) then outputting that result?

Reply ↓

Kevin H June 14, 2022 at 9:32 am
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.
```
LogicalParameter= type logical meta [
    Documentation.FieldCaption = "My Logical Parameter",
    Documentation.AllowedValues = {true,false}
];
```
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?
Reply ↓
1. Ben Gribaudo Post authorJune 15, 2022 at 7:57 am
  
  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. 🙁
  
  Reply ↓