Power Query M Primer (part 3):
Functions: Function Values, Passing, Returning, Defining Inline, Recursion

, , , ,

Not only can you define and invoke functions (as we covered in part 2), you can also pass them around. The ability to pass a function around without invoking it gives lots of flexibility.

Sounds complex? Yes, in words, but not necessarily in practice. If you’ve touched the Power Query M language, you’ve probably already passed functions around—just perhaps without realizing about it.

Then there’s the mysterious each. It shows up a lot in code generated by the query editor. What does it actually mean or do? Turns out, it’s a handy shortcut that can simplify code you write.

We have ground to cover. Let’s get going!

Series Index

Function Values

In part 2, we described a function as “an expression that ultimately produces a value.” This is accurate, but there’s more to the story: there’s an intermediate step in the process. Technically, a function expression is evaluated to something called a function value, which can be stored in variables and passed into and returned from other functions. Invoking the function value executes the function’s body which produces the ultimate function return value.

You could think of it this way: The function expression is code you write. The function value is the technical manifestation of the function expression you wrote. The function return value is the output of executing the function you wrote.

  Multiply = (x, y) => x * y,
  Result = Multiply(10, 20)

Above, the function expression (x, y) => x * y is evaluated to a function value which is assigned to variable Multiply. When this function value is invoked on the next line (Multiply(10, 20)), the functions body is executed and the value it produces is returned. This value is then assigned to Result.

Whew! That sounds complex. The example is a lot simpler than the words it took to describe it!

Speaking of simplicity: To keep things simple, we usually omit the word “value” when talking about “function values.” When a function expects a function value as one of its parameters, we’ll usually just say that that parameter is “supposed to be a function” instead of saying “supposed to be a function value” even though the latter is really what is expected. The same goes when we talk about “passing functions around.” We’re technically “passing function values around,” but we usually take the shortcut of leaving the word “values” off.

Passing Functions

The ability to pass a function into another function is powerful. The other function can implement a generic algorithm that’s widely applicable then use the function passed into it to custom its behavior so that it’s relevant to our particular situation.

Take, for example, the idea of adding a new column to a table. Table.AddColumn implements the generic formula which makes this possible. However, we want to customize Table.AddColumn’s behavior so that we can control the values used for the new column. To enable us to do this, Table.AddColumn allows us to pass it a function as a parameter. It then invokes this function once per table row, passing the passed-in function the current row as its argument and then using the value it returns as the value for the new column for the current row.

  Source = #table( {"Col1", "Col2"}, { {1, 2}, {3, 4} } ),
  ColumnCreator = (row) => row[Col1] + row[Col2],
  AddColumn = Table.AddColumn(Source, "RowTotal", ColumnCreator)

Table.AddColumn handles the heavy lifting of adding a new column (the generic algorithm). We customize its behavior through the function we provide which is invoked on an as-needed basis (in this case, once per row). We don’t have to write a function that handles all the responsibilities associated with adding a new column, just a simple function that takes a single row and produces a single value. Powerful, eh? Makes our lives much easier.

In some other languages, a similar effect can be achieved using delegates or function pointers.

Inline Definition

Since a function is an expression and expressions are allowed in parameter lists, we can define functions inline, directly in a parameter list.

Below, the new column function is defined in the argument list instead of first being assigned to a variable. As far as Table.AddColumn is concerned, the effect is the same as the previous example.

  Source = #table( {"Col1", "Col2"}, { {1, 2}, {3, 4} } ),
  AddColumn = Table.AddColumn(Source, "RowTotal", (row) => row[Col1] + row[Col2])

Shortcuts: each & _

In life, each person is special. In Power Query M, each is also special—because it simplifies a common M code pattern.

Defining a function that accepts a single argument is such a common need in Power Query M that the language defines a shortcut to simplify it: Keyword each is shorthand for (_) =>.

Since we haven’t talked about records yet, we’re jumping ahead of ourselves—but I’ll go ahead and let you in on another shortcut: [FieldName] without a name directly before it is shorthand for _[FieldName].

Each of the below statements is equivalent. Each successive statement uses more concise syntax which makes it easier to read.

(_) => _[Col1] + _[Col2]
each _[Col1] + _[Col2]
each [Col1] + [Col2]

We can simplify our previous inline definition example using these shortcuts. How’s this for succinctness?

  Source = #table( {"Col1", "Col2"}, { {1, 2}, {3, 4} } ),
  AddColumn = Table.AddColumn(Source, "RowTotal", each [Col1] + [Col2])

Why the name each? My guess is the name comes from the fact that each is often used to simplify function definition where the function will be invoked once per item in the input data set (for example, Table.AddColumn invokes a single-argument function once for each table row). Regardless of its etymology, each can be used any time you want to define a single argument function, whether or not it will be called once per item.

Returning Functions

Functions can also return functions.

() => (x, y) => x * y

Does the above look pointless to you?! Returning functions becomes much more advantageous when we take advantage of something called closure. A closure allows a function to remember the values of variables that were in scope when it was defined.

Below, when we invoke the outer function and pass it a value for x, the inner function that’s returned remembers the value of x. When we invoke the inner function, we only need to pass it a value for y. It will then multiply the remembered x value by y.

(x) => (y) => (x * y)

Invoking the above, passing 5 as the value for x returns a function that behaves as though it were defined like this (notice how the function remembers the value of x that used when it was generated):

(y) =>
    x = 5
   (x * y)

Library function List.Transform expects two arguments. First, the source list; then a function that will be invoked once per list item to transform that item to its new value. This function will be passed the value of the current list item as its argument.

We want to transform a list of numeric values, reducing them by a certain percentage. One way to do this is to define a function that accepts the discount percentage and returns a function that accepts a value and reduces it by the remembered discount percentage. This returned function will be passed in to List.Transform.

  Source = { 1, 2, 3, 4, 5 },
  CalculatorGenerator = (discountPercentage) =>
    (value) => (1 - discountPercentage) * value,
  HalfOff = CalculatorGenerator(0.5),
  Result = List.Transform(Source, HalfOff)

Above, HalfOff is a function that accepts a single argument and reduces it by 50%. HalfOff remembers the discountPercentage value that was provided to CalculatorGenerator when HalfOff was generated.

Technically, it’s possible for a function to return a function which is immediately invoked instead of first being assigned to a variable. Say, we want to use our discount calculator logic to compute a 75% discount on a single value and we don’t have a need to use that same discount function anywhere else. We can skip assigning the discount function to a variable before invoking it. Instead, we can invoke it immediately after we generate it:


Recursive Functions

On occasion, you might want a function to call itself. In order for a function to reference its own name from inside itself, simply prefix the reference with “@”:

  SumConsecutive = (x) => if x <= 0 then 0 else x + @SumConsecutive(x - 1),
  Result = SumConsecutive(4)

Next Time

There’s so much more to cover! Next time, we’ll learn more about identifiers (think: variable names).

2 thoughts on “Power Query M Primer (part 3):
Functions: Function Values, Passing, Returning, Defining Inline, Recursion

  1. Siraj

    Ben, this was a bit too dense. May be, you could have taken some real-life examples and walked them through rather than going through theoretical examples. But anyway, as I gain more familiarity, your explanations would sound simpler to me, I guess. Thanks for your efforts.

  2. Peter McLean

    Hi Ben. Thank you for doing a great job at simplifying a new language that to me on first impressions seems to have found inspiration from a mash up of programming language principles, i.e. from set based to object oriented to functional. I am now learning and putting into practice your teachings in Power Query M Primer Part 1and 2, and I am much looking to learning from the rest of your series. Please keep the great series going as it fills the void in lack of good content on the M Language. Also you will have me as a very enthusiastic 40 years ICT experienced 65 year old student and strong supporter. Thanks from Aussie mate. :-).


Leave a Reply

Your email address will not be published. Required fields are marked *