Power Query M Primer (Part 22): Identifier Scope II – Controlling the Global Environment, Closures

• , , , ,

As we learned last time, normally, M code is evaluated in a global identifier resolution scope consisting of all shared members + the standard library. Also, normally, we can’t inject additional identifiers into this global environment. Normally isn’t always. Today, we learn about the exception: where both of these normalities do not apply.

That’s not all: Did you know that M has a mechanism for remembering how to access variables that later go out of scope? Closures open up powerful options, particularly when generating functions…and even enable building an object-like programmatic construct that maintains internal private state and is interacted with through a public interface (kind-of, sort-of somewhat like an object from object-oriented programming!).

Series Index

A Clean Environment

Remember the very first example from the previous part in this series? We imagined the below expression living in a world with no standard library, no other queries (a.k.a. no section members) and devoid of any other kind of global environment (so not even the standard library is present).

[
  a = 1,
  b = 2,
  c = 3
]

Turns out, we don’t have to imagine this. Thanks to Expression.Evaluate, we can make such a minimalistic environment a reality.

This standard library function evaluates arbitrary M code, which is provided in textual form as its first argument, returning the result as its output.

Expression.Evaluate("10 + 20") // returns 30

What’s passed in to Expression.Evaluate defines the entirety of the environment that the provided expression is evaluated in.

The specified expression is evaluated in a section-less world. No sections or section members are present. (This makes sense, as the M code you pass in is an expression, not a section document.) If you’d like to prove that no sections are present, try Expression.Evaluate("#sections"), and notice that it returns an empty record.

By default, the global environment used when the expression is evaluated is also empty. No standard library is present. Try Expression.Evaluate("#shared"), which also returns an empty record.

Populating the Global Environment

You can choose to add identifiers to this global environment. This is the purpose of Expression.Evaluate‘s optional second argument. If provided, it should be a record where each field’s name is the name of an identifier to add to the global environment created by Expression.Evaluate, with the corresponding field value being the value to associate with that identifier. To emphasize: This record defines the entirety of the global environment used when evaluating the specified expression.

Below populates the global environment used inside Expression.Evaluate with a (set to 10) and b (set to 20).

Expression.Evaluate(
  "a + b", 
  [a = 10, b = 20] // defines the global environment that's used when evaluating the provided expression
) // returns 30

You can see that the record’s contents are injected into, and define the entirety of, the global environment used by Expression.Evaluate by asking it to evaluate #shared.

Expression.Evaluate("#shared", [a = 10, b = 20]) // returns [a = 10, b = 20]

If you want to evaluate an expression that references functions from the standard library, you’ll need to add the relevant functions to that global environment.

Expression.Evaluate("Text.Upper(""hi"")") // errors because Text.Upper is not present in the global environment being used
	
Expression.Evaluate("Text.Upper(""hi"")", [Text.Upper = Text.Upper]) // returns "HI"

Expression.Evaluate‘s second argument gives fine-tooth control over the environment used when evaluating the specified expression. Expression.Evaluate‘s purpose is to process arbitrary M code, which in some cases may come from (or be derived from) untrusted sources (say, user input stored in a database). In cases like these, limiting the functions that the expression can use to only those you expect it to (want it to) use is an important security measure to help guard against an injection attack (think of a SQL injection attack, Power Query style).

What if fine-tooth control over what the expression can execute isn’t important in your context? Rather, you want the expression to be evaluated in a “normal” global environment—that is, the same global environment you’d get outside Expression.Evaluate. Is this possible?

Easy! Set Expression.Evaluate‘s second argument to #shared.

Expression.Evaluate("Text.ToUpper(""hi"")", #shared) // returns "HI"

(Remember from the last post that #shared returns a record containing one field per identifier in the global environment? This is the exact format Expression.Evaluate needs to inject these identifiers into the global environment it sets up, so referencing #shared here works perfectly. Just beware of the potential security ramifications.)

Closures

Now, let’s try something which, if we did it to someone else, would be a mean practical joke—but as we’re doing it to ourselves, it’s okay, and will illustrate an important concept. Let’s create a global environment where Text.Lower is actually defined as Text.Upper.

Expression.Evaluate("Text.Lower(""Hello"")", [Text.Lower = Text.Upper]) // outputs "HELLO"

Again, don’t do this to someone else. That would be quite unkind.

Let’s keep going with this. In the outer environment, we’ll define a function of our own that formats text. This function, which we’ll also pass in to Expression.Evaluate, internally uses Text.Lower.

let
  FormatText = (input as text) as text => Text.Lower(input),
  EvaluatedExpression = Expression.Evaluate(
    "FormatText(""Hello"")", 
    [
	   FormatText = FormatText,
	   Text.Lower = Text.Upper
	]
  )
in
  EvaluatedExpression // what's returned here?

So, the inner global environment created by Expression.Evaluate contains exactly two identifiers:

  • Our FormatText function, which uses Text.Lower.
  • Text.Lower, which maps to the standard library’s Text.Upper.

If, inside Expression.Evaluate, we invoke FormatText("Hello")—as the above example does—what will be output?

hello

Wait!!! Shouldn’t that be “HELLO” because inside Expression.Evaluate, Text.Lower actually outputs upper cases text, and FormatText uses Text.Lower?

Ah! This is the lesson that we’re driving at here: FormatText is not using the Text.Lower that’s present inside Expression.Evaluate‘s global environment (which up-cases text); rather, it’s using the Text.Lower that existed in the environment where FormatText was defined.

Remembering the Defining Context

In M, functions “remember” the context in which they were originally defined, even when they are later invoked from a different context. In the nomenclature of programming language theory, this technique for remembering context is called a closure.

When FormatText is defined, a closure is “captured” which enables FormatText to always use the Text.Lower that existed in its global environment when it was defined (the one that lowercases text). This holds true even though later when FormatText is invoked, the Text.Lower that’s in the global environment at that point in time is the one that uppercases text. This latter Text.Lower is irrelevant to FormatText—it doesn’t use it or need it to be present; the Text.Lower that FormatText uses is the one referenced by its closure.

Closures aren’t limited to just when Expression.Evaluate is used. Below, Generator is a function that takes in an hourly wage rate and returns a function that takes in hours worked, multiplies it by the hourly rate (which was “captured” by a closure), then returns the result.

let
  Generator = (hourlyRate as number) as function =>
    (hoursWorked as number) as number => hourlyRate * hoursWorked,
  FifteenPerHour = Generator(15)
in
  FifteenPerHour(40) // returns 600

The concept is simple yet may feel mind-bending.

To help grasp what’s going on, let’s walk this backward. FifteenPerHour is a variable pointing to a function. We can invoke FifteenPerHour, passing it the number of hours worked, and it will return the amount of wages to be paid.

Where does the function for FifteenPerHour come from? Instead of it being defined the way we’re typically used to, like “FifteenPerHour = (hoursWorked) => hoursWorked * 15“, it is defined as the result of a function. Strange as this may feel, this is perfectly valid: a function can return a function, and the function that’s returned can be stored in a variable.

Generator, the function that’s invoked to generate FifteenPerHour‘s function, takes an argument that specifies the hourly wage rate to use. When that function generates the function that actually computes total wages (FifteenPerHour), this rate is “captured” by a closure, which enables the generated function to reference it throughout the generated function’s lifetime.

When FifteenPerHour(40) is later invoked, nowhere is the hourly rate visible; that amount is not in context at the point of invocation. However, FifteenPerHour knows (remembers) that the rate is 15, thanks to its closure.

Transformer Functions

Closures can be particularly handy when transforming values. Say you have a list which you want to transform using List.Transform. That function’s second argument is the transformer function to use. It will be called once per list item, each time being passed the current item as its single argument, which it should then transform and return.

List.Transform({ "-abc-", " def "}, Text.Trim) // outputs { "-abc-", "def" }

This is great in many cases (like the above), but what if you want to configure the behavior of the transformer function? For example, in the case of Text.Trim, you may want to set its optional second argument so that the method trims away a different character.

One option is to build a single-argument function that returns a function which calls Text.Trim with its second argument hard-coded

let 
  TrimHyphens = (textToTrim as text) as text => Text.Trim(textToTrim , "-")
in
  List.Transform({ "-abc-", " def "}, TrimHyphens ) // outputs { "abc", " def " }

This works great, but your new function is hard-coded to trimming one specific character. What if you wanted to make it configurable? Thanks to closures, this is easy:

TrimCharacter = (characterToTrim as text) as function => 
   (textToTrim as text) as text => Text.Trim(textToTrim , characterToTrim)

When TrimCharacter(“-“) is invoked, it returns a single-argument function, which, when invoked, trims away the specified character from the text passed to it.

let
  …,
  TrimHyphens = TrimCharacter("-") // generates a function that trims hyphens
in
  TrimHyphens("-abc-") // invokes the generated function, returning "abc"

Or, as a one-liner:

TrimCharacter("-")("-abc-") // generates a function that trims hyphens, then invokes it, returning "abc"

Plugging the new TrimCharacter function into our list transformation scenario, we can use it to generate a single-argument function that trims away whatever character we specify. Below, we use it to trim away hyphens. List.Transform is happy: it’s being passed a single-argument function that takes the current list item as its input and returns the transformed result. We’re happy because we’re using a generic, reusable function to generate the transformer function for List.Transform.

let 
  TrimCharacter = (characterToTrim as text) as function => 
    (textToTrim as text) as text => Text.Trim(textToTrim , characterToTrim)
in
  List.Transform(
    { "-abc-", " def "}, 
    TrimCharacter("-") // generates a single-argument function that trims hyphens, which is then invoked by List.Transform on each list item
) // outputs { "abc", " def " }

All this, thanks to closures, which enable the generated function to “remember” the characterToTrim value that was passed in to the generator function.

Kinda, Sorta Object-Like Behavior

The M language’s support for closures enables a type of object-oritented-like behavior: the ability to create and interact with programmatic structures that expose public interfaces and maintain private internal state.

Of course, since M’s values can’t be modified, each interaction produces a new structure which must be used for the next interaction. (In contrast, an object-oriented language allows the same object instance to be directly manipulated, and manipulated multiple times.)

Let’s imagine a more complex scenario around wage computation. We want an object-like “timecard” entity that allows us to set/change the hourly rate, record hours worked and fetch the total wage amount.

Imagine something that we  can interact with like this:

let
  …
  InitialWage = Timecard(10), // create a new time card and sets the initial hourly wage rate 
  FirstWeek = InitialWage[RecordHoursWorked](40), // records week 1's work
  PayRaise = FirstWeek[SetRate](25), // raises the wage rate for subsequent work
  SecondWeek = PayRaise[RecordHoursWorked](40), // records week 2's work
  ThirdWeek = SecondWeek[RecordHoursWorked](35), // records week 3's work
  TotalWages = ThirdWeek[TotalWages]() // get total wages earned
in
  TotalWages // returns 2275

The above starts with a timecard being initialized, with the pay rate set to 10. What’s returned is a record consisting of several fields, each of which contains a function. These functions form the public interface for interacting with the timecard.

Screenshot of "public interface" record

This new timecard, saved as InitialWage, is used for FirstWeek‘s recording of hours worked, which is done by using the timecard’s RecordHoursWorked function. This method returns a new timecard with the same set of fields as the previous card, just with the card’s internal state updated to reflect the hours worked.

Next, there’s a PayRaise, which is recorded by invoking SetRate from the FirstWeek timecard’s record. The net effect is another timecard, this one internally updated so that it will record future hours worked at the new pay rate.

Then SecondWeek and ThirdWeek‘s hours worked are recorded by invoking RecordHoursWorked on the timecard from the preceding (respectively) step.

Lastly, TotalWages is output by invoking TotalWages on ThirdWeek‘s timecard. Unlike the card’s other methods, this one doesn’t return a new timecard; rather, it directly returns the amount of total wages as a numeric value.

Internally, timecard maintains the necessary internal state to compute total wages. How it does this is hidden to us, the consumer, and so something we can’t manipulate. Our ability to interact with the timecard is defined by the interface of public methods provided in its record.

The Innerworkings

So how does timecard work?

Timecard = (initialRate as number) => 
  let
    NewTimecard = (state as record) => 
      [
        SetRate = (rate as number) => 
          @NewTimecard(state & [Rate = rate]),
        RecordHoursWorked = (hours as number) => 
          @NewTimecard(state & [TotalWages = state[TotalWages] + state[Rate] * hours]),
        TotalWages = () => 
          state[TotalWages]
      ]
in
  NewTimecard([Rate = initialRate, TotalWages = 0])

Function Timecard takes an initial wage rate which it stores in a state record. This record’s fields, invisible to the outside world, are the approximate equivalent of an object’s private fields.

The state record is then passed to a function defined inside Timecard named NewTimecard. This function returns a record containing the timecard’s “public interface” of three functions, which defines the entirety of how outside code can interact with the card.

Two of these functions, SetRate and RecordHoursWorked, when invoked, call same NewTimecard method, passing it a state record that’s been appropriately updated to reflect the new wage rate or hours worked (respectively). It’s closures that make this possible: from the outside, when you call SetRate or RecordHoursWorked, you have no knowledge of, or access to, this NewTimecard method or the existing state record—but thankfully those two functions do, thanks to closures.

While interesting, typically we don’t write M code this way. We don’t need to. Usually, our interest is in producing a data set, which is achieved by flowing data through a chain of function calls. In contrast, incrementally “manipulating” state by making a series of function calls (as in the last example) is more applicable to when something needs to be programmatically set up for later use.

Like what? For one, how about query folding? When you implement query folding support, Power Query needs an interface to interactively communicate with your implementation, preparing it to produce data. Also, your implementation probably has internal state which Power Query’s folding infrastructure shouldn’t touch or need to know about. This is exactly the kind of situation this style of M coding suits well.

Next Time

This style of sort-of, kind-of object-like M code is foundational for implementing query folding…and covering the basic concepts of implementing folding is the topic for next time.

You may have no plans to ever implement query folding. That’s okay. Understanding the basics of how it’s implemented will help you better understand how it works—and considering the significance query folding holds in the Power Query world, growing your knowledge in this area is a good investment to make.

Related posts:

  1. Power Query M Primer (Part 21): Identifier Scope & Sections
  2. Power Query M Primer (Part 25): Extending the Global Environment
  3. Power Query M Primer (Part 23): Query Folding I
  4. Power Query M Primer (Part 4): Variables & Identifiers

Leave a Reply

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