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.

1
2
3
4
5
let
  Multiply = (x, y) => x * y,
  Result = Multiply(10, 20)
in
  Result

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 function’s 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.

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

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.

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

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.

1
2
3
(_) => _[Col1] + _[Col2]
each _[Col1] + _[Col2]
each [Col1] + [Col2]

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

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

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.

1
() => (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.

1
(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):

1
2
3
4
5
(y) =>
  let
    x = 5
  in
   (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.

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

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:

1
CalculatorGenerator(0.75)(10)

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 “@”:

1
2
3
4
5
let
  SumConsecutive = (x) => if x <= 0 then 0 else x + @SumConsecutive(x - 1),
  Result = SumConsecutive(4)
in
  Result

Next Time

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

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

    Reply
  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. :-).

    Reply
  3. Jeff Weir

    Hi Ben. I’m struggling to understand the “ (discoutPercentage) => (value) => ” bit in your example below.

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

    So a function argument can itself reqiure a function argument? Can you walk though the execution of this?
    For instance, working backwards from the Result step, the List.Transform function says “Go get the items from Source, and put them through the HalfOff function”.
    And the HalfOff function says “pass these values to the CalculaterGenerator function, and tell it to use the setting of 0.5”
    And the CaclucatorGenerator function says “Hey, I need a parameter. 0.5, you say?” And the *parameter itself* says “I need a parameter”.

    How does that work? How does (value) know to use the items in source?

    Reply
    1. Ben Gribaudo Post author

      It might help to think of what’s going on it two steps:

      1. Determining the value of HalfOff.
      2. Invoking that value once per Source list element.

      Let’s start with a simpler scenario:

      HalfOff = (value) => (1 - 0.5) * value,
      Result = List.Transform(Source, HalfOff)
      

      To produce the value passed in as List.Transform’s second argument, M’s interpreter evaluates the function expression defining HalfOff. This evaluation, which occurs once, produces a function value which is then passed in to List.Transform. List.Transform, in turn, invokes this function value once per Source list element. So, evaluating the function expression to function value takes place exactly once but invoking that value can occur multiple times.

      With:

      CalculatorGenerator = (discountPercentage) =>
          (value) => (1 - discountPercentage) * value,
      HalfOff = CalculatorGenerator(0.5),
      Result = List.Transform(Source, HalfOff)
      

      In essence, CalculatorGenerator is being asked to generate the value for HalfOff. Just like our simpler scenario, this evaluation occurs only once. When CalculatorGenerator(0.5) is called, it returns a function with the value of discountPercentage “memorized” (the related technical term is closure). In a sense, you could think of CalculatorGenerator(0.5) as returning (value) => (1 - 0.5) * value (where the value of discountPercentage has been “hard-coded” in, as though discountPercentage was a template placeholder that was replaced with the specified value when CalculatorGenerator(0.5) was asked to provide the function).

      As far as List.Transform is concerned, in both scenarios, the second argument passed to it is simply a function (technically, a function value) that accepts a single argument (the list item to transform).

      Does this help?

      Reply
      1. Jeff Weir

        Actually I think what helped most was a very close rereading of the post. I just hadn’t understood fully about passing “function values” around. (Would ‘Function Step’ or ‘Function Transform’ be a better name?)

        I come from the world of self-taught VBA in Excel, and so hadn’t heard of Closures or even First Class Functions. So I watched a quite good video at https://youtu.be/kr0mpwqttM0 that explained this concept using Python and JavaScript (neither of which I have any experience in), and it kinda sunk in when he assigned a function to a variable, and made the point that he wasn’t assigning the *result* of a function to a variable. And then after that, he could treat that variable as if it were a function, meaning he could pass some argument into it just by going VariableName(SomeValue). And only at that point was anything being executed.

        I also think the syntax sugar in the List.Transform was confusing me.

        This fully fleshed out example of your previous snippet seems to help it sink in for me:

        let
            Source = { 1, 2, 3, 4, 5 },
            MyFunction = (x) => (y) => (x * y),
            MyX = MyFunction(5),
            Output = List.Transform(Source, (_) =>  MyX(_))
        in
           Output
        

        …especially when I step through the Applied Steps pain on the right hand side, and see that the MyFunction step wants an X, but the MyX step only wants a Y (meaning it already has it’s X value)

        Reply
  4. Celia

    Hello, Ben and all! I Just want to say that I am enjoying everything on this page and series, from the post content to the readers’ comments.
    Ben, I appreciate your effort of explaining everything in detail and with simple examples, as much as possible. Like Jeff, I also come from the world of self-taught VBA. Sometimes I have to read several times until I am confident that I understood fully.
    Ben, if you ever want to go back to enhance the content of these posts, it would be helpful to have the actual result values of applying a script to a specific value, list, column or table.
    I am extremely grateful for the content that you already posted. Thanks a lot for that!

    Reply
    1. Ben Gribaudo Post author

      Thank you for your kind words and the feedback, Celia! Showing more example results is a great idea. I will keep it in mind for a future revision.

      Reply
  5. Scott Walker

    I have a question regarding the recursive function example:

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

    I would have guessed that no matter what number you input for x when invoking SumConsecutive, it would always yield the return value 0. My reasoning is that, as the function iterates and x decreases, eventually x will be 0. Then, when it runs the the logic if x <= 0, it will return 0 given the THEN statement. However, clearly the function retains the iterative sum. I'm not quite sure how it does so, and how it knows to return that sum instead of 0 when the THEN statement instructs it to return 0.

    I hope that's clear. Thank you for this primer series. It is fantastic. I'm a self-learner using Power Query and DAX in a standard office setting to up my department's analytics game. This is a great fundamentals review.

    Reply
    1. Ben Gribaudo Post author

      Thank you for your kind words, Scott!

      Don’t be discouraged! Recursing functions can be stretching to reason about. 🙂

      Does this help?

      • When SumConsecutive(4) is invoked, its x will equal 4 and the value it returns (which will be saved into Result) will be x [i.e. 4] plus the value returned by calling SumConsecutive(x [i.e. 4] - 1).
      • When SumConsecutive(3) is invoked, its x will equal 3 so the value it returns will be 3 plus the value returned by calling SumConsecutive(x [i.e. 3] - 1 ).
      • When SumConsecutive(2) is invoked, its x will equal 2 so the value it returns will be 2 plus the value returned by calling SumConsecutive(x [i.e. 2] - 1 ).
      • When SumConsecutive(1) is invoked, its x will equal 1 so the value it returns will be 1 plus the value returned by calling SumConsecutive(x [i.e. 1] - 1 ).
      • When SumConsecutive(0) is invoked, its if statement will result in 0 being returned.

      4 + 3 + 2 + 1 + 0 = 10

      Reply
      1. Scott Walker

        Ah that is clear. The variable Result to which we assigned the call SumConsecutive(4) retains the value of each iterative call. Great, thank you very much!

        Reply
  6. Alejandro

    Dear Sir,
    In first example, how come we didn’t pass the table name through to defined “row” variable. I assume we need to clarify the TableName[Col1] + TableName[Col2] so “row “is the variable that is standing in for table?
    Example:  AddColumn = Table.AddColumn(Source, "RowTotal", ColumnCreator(Source))

    Or I guess what is “row” variable really doing in this case? Does the AddColumn ignore it and replace it with each?

    This maybe because its not not a table but a record or list which you alluded earlier on that we will find out more in the future. I understand the “each” refence example that you put below, but just trying to understand what purpose defining “row” has when we don’t explicitly pass an argument using “row”

    Reply
    1. Ben Gribaudo Post author

      Hi Alejandro,

      Table.AddColumn expects the value that’s passed as its second argument to be a function that, when invoked, takes one argument. AddColumn will then invoke this function one time for each row in the table. Each time it invokes the function, it will pass the contents of the current row, stored in a record, to the function.

      Imagine Table.AddColumn(Source, "RowTotal", ColumnCreator) is run on a three row table. Behind the senses, something like the following will take place:

      AddColumn calls ColumnCreator for the first row, passing in the first row’s values as a record–like: ColumnCreate(firstRow). It does the same for the second row–like ColumnCreate(secondRow), and similarly for the third. Each time, the result returned from the ColumnCreate invocation is used to set the new column’s value for the given row.

      The table name doesn’t need to be passed to ColumnCreate because that function will be automatically be passed each row’s contents at the appropriate time.

      Does this help?

      Reply
  7. Kanstantsin Chernik

    Hi Ben,

    Thank you for the great blog!. I believe I learned here more than on MS site.
    I am trying to implement a mock function which can simulate multiple http responses.
    The problem I struggle with is that all variables are immutable there is no place I can store the state in a function.
    This is just a basic idea. Of course, this code doesn’t work:

     MockContentsGetter = (content as list) => 
            let
                i = 0,
                fn = (query as text) => 
                    let
                        res = content{i},
                        i = i + 1 // here is the problem. It is executed before the previous line and immutable
                    in
                        res
            in 
                fn
    
    Reply
  8. Cody B. Buehler

    Hi Ben,

    I’m having trouble actually seeing how _ works. I understand how the each syntax works but can’t find anything to break down what is actually happening when _ is invoked. Could you provide any additional information?

    Reply
    1. Ben Gribaudo Post author

      Hi Cody,

      _ is just a variable holding the function argument passed to each. Instead of naming the argument something like input, the designers of M called it _.

      Example: The function each _ * 10 takes its input argument (_) and multiplies it by 10. If you wanted to define an equivalent function without using each, you could write: (_) => _ * 10. If you preferred using input as the argument name (instead of _), you could instead do: (input) => input * 10

      Does that help?

      Reply
  9. Sting Guo

    Hello Ben,

    Why i could not find any related topics of books about Powerquery? Do you have any recommended source of learning the above mentioned topics?

    BR

    Reply

Leave a Reply to Peter McLean Cancel reply

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