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

, , , ,

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 = (DiscountPercentage) => 1 - DiscountPercentage,
  Result = OrderAmount * NonDiscountedPercentage(DiscountRate)
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:

  1. Create a new blank query.
  2. Choose advanced editor.
  3. Replace whatever is there with your function’s expression, starting with the parenthesized argument list.
    Screen short showing advanced query editor being used to define a top-level function
  4. After exiting the advanced editor, rename the so-called query you just defined to something more appropriate.
    Screen shot showing top-level function being renamed
  5. Now, you can reference this function by the name you just defined from elsewhere in your project.
    Screen shot of advanced editor showing just-defined function being referenced from another expression

Query list showing just-renamed functionNotice 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 StringifyNameSelect 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.

If you are in the query editor, leave all three fields blank and then click invoke, the query editor will populate the first two fields with null before calling the function. It gave you the option of providing values for them. Since you didn’t, it provided safe defaults for you. In code, invoking this screen with all three fields left blank results in the following being executed:

MyFunction(null, null)

Notice the nulls the query editor inserted for the first two parameters. In contrast, the editor didn’t provide a third parameter because that parameter is truly optional .

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

Leave a Reply

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