Power Query M formula language: Functions

Declaring a function in a let expression

The following example declares a function in a let expression. The entire expression results in the value 31:

let
   func = (x, y) => 2*x + 3*y,
   a = 5,
   b = 7
in
   func(a, b)

Github repository about-Power-Query-Formula-M, path: /language/functions/first.M

Providing parameter values

If the function is referred to without specifying the values for the parameters, the user is prompted to provide these:

let
   func = (param_1, param_2) => param_1 + param_2
in
   func

When executed, such a dialog is presented:

Declaring a function in a record

Similarly, it is also possible to declare a function in a record:

[
   func = (x, y) => 2*x + 3*y,
   a = 5,
   b = 7,
   result = func(a, b)
]

Github repository about-Power-Query-Formula-M, path: /language/functions/record.M

When evaluated in Excel, for example with this VBA code, this formula evaluates to:

Recursive functions

Recursive functions can be defined using the at-sign (@) which starts an inclusive identifier reference. This is required to gain access to the environment that defines the identifier that is being used:

let
   fibonacci = (n) =>
      if      n = 0 then 0
      else if n = 1 then 1
      else @fibonacci(n-2) + @fibonacci(n-1)
in
  [
    #"fib( 3)" = fibonacci( 3),
    #"fib( 9)" = fibonacci( 9),
    #"fib(11)" = fibonacci(11),
    #"fib( 4)" = fibonacci( 4)
  ]

Github repository about-Power-Query-Formula-M, path: /language/functions/recursive.M

This example produces:

Closures

let
   twice = (n)        // twice is a function that
   => ()              // returns a function
   => 2*n,            // that returns twice the amount of
                      // the value with which twice was invoked
   eight = twice(4),
   six   = twice(3)
in
   eight() + six()