In our last post Imke and I discussed environments of nested records and let-expressions. The current post covers one native M function, which can define its own environment: Expression.Evaluate(). To explain this function and its environment in detail, we also have to take a look at one ancillary topic: the intrinsic variable #shared. Let’s go…

You can download all code samples from here. If you already have my Power Query editor for Notepad++ (or want to build it) you can open the file in Notepad++. Otherwise open it in a simple text editor and paste it into the Advanced Editor of Power Query or Power BI Desktop.

The global environment

In the last two posts (Part1 and Part2) we discussed environments of expressions in records and let expressions. In addition to these very individual environments, there is (almost) always a global environment. Take a look at the following expression and its result:

There are two reasons, why the expression Table.FromRecords({[A=1, B=2],[A=3, B=4]}) returns a table with two columns and two rows:

1. The function Table.FromRecords() (like any other native M function) is part of the global environment.
2. The expression Table.FromRecords({[A=1, B=2],[A=3, B=4]}) is evaluated with the global environment being part of the expression’s environment.

That might sound a bit strange. But you will see later, when we talk about the function Expression.Evaluate(), what happens, if there is no global environment. Then come back to this line and read it one more time 🙂

What’s part of the global environment?

Knowing, that there is a global environment might make you think what is part of it. If you want to see the components of the global environment, you can use the (so called) intrinsic variable #shared. #shared returns a record containing all names and values of all the queries (tables, constants, records, lists, native M functions, custom functions, etc.) in the current Excel/ Power BI Desktop file.

All these components are part of the global environment. This is the reason why you can reference a Power Query queries‘ result in another Power Query query. You can’t influence #shared in Power BI Desktop/ Power Query by writing code, but you can create new queries, which then automatically belong to #shared. Now, knowing what the global environment is and knowing about #shared, let’s continue with the function Expression.Evaluate(), which can define its own environment.

Expression.Evaluate()

This M function evaluates a text expression and returns the evaluated value. You can find the official documentation of this function here. Before we move on, we have to give big credits to Chris Webb for his excellent post about Expression.Evaluate(), which very much enlightened us 🙂 . Take a look at this very simple example of how to use this function:

Even though „1 + 1“ is a text, the function evaluates the expression and returns the value 2. This can get pretty handy, when you for example have to create the code to be evaluated on the fly.

Expression.Evaluate() and the global environment

Now let us get back to our former expression Table.FromRecords({[A=1, B=2],[A=3, B=4]}) and evaluate this inside Expression.Evaluate():

Instead of returning the expected table, we get the error message, that the function Table.FromRecords() doesn’t exist in the current context. Replace current context by current environment and it gets a bit clearer: The required function doesn’t exist in the current environment. Expression.Evaluate() is not evaluated in the global environment and this is why it doesn’t even know the function Table.FromRecord() exists. Its environment is empty.

How can we resolve that? Let’s take a look at the syntax of Expression.Evaluate():

Expression.Evaluate(expression as text, optional environment as [...]) as any

The function has a second, optional parameter: Environment. This parameter has to be a record. But how to define the environment with a record? There is good news for you. You already know a variable, that contains all the M functions and returns a record: #shared! Using #shared as second parameter resolves the problem:

But not all environmental problems come from the global environment.

Expression.Evaluate() and non global environments

The global environment is not the only possible environmental problem, when it comes to Expression.Evaluate(). Take a look at the following script:

The error message is the same (only the error code is slightly different), as in the example before, but this time it’s not about the global environment, but about the environment of the let expression. The expression behind variable Source cannot access the variables MyVariableA and MyVariableB, because the environment within the Expression.Evaluate() function is empty. You might think, that we could resolve this problem the same way we did last time: by adding #shared?! Unfortunately this is no solution. Even if you include #shared, you get the following error message:

The variables MyVariableA and MyVariablesB are not part of #shared, as this intrinsic variable only includes the results of queries, not single variables within the queries. So, how could a solution look like? Take a look at the following script:

As you can see this script delivers the desired result: 3! But honestly, what does this [MyVariableA = MyVariableA, MyVariableB = MyVariableB] mean? This is something we learned from Chris and the next screenshot will unravel the mystery:

The definition in squared brackets pulls the outer variables MyVariableA and MyVariableB into the environment of the Expression.Evaluate() function and creates a connection between those outer variables and the variables from inside the Expression.Evaluate() function. Usually the inner and outer variables have the same name (e. g. MyVariableA = MyVariableA) what made it hard to understand expresions like [MyVariableA = MyVariableA], but now you know what this syntax is used for. Please notice, that the order plays an important role: It has to be [InnerVariable = OuterVariable]. If you try to change the order, you will get an error message.

Expression.Evaluate with combined global and non global environments

You have seen how to define the global environment inside Expression.Evaluate (by adding #shared) and how to add variables from the same query to the Expression.Evaluate function. Now let’s push it a bit further. What if you need both combined? Let’s say, you want to access outer variables and additionally you want to use a native M function, to calculate a sum of a list:

We need to get the list MyListA inside the environment of Expression.Evaluate(). Additionally we need to add the global environment (#shared) to Expression.Evaluate(), because otherwise List.Sum() does not exist. We’ve got to do that in three steps:

1. Creating a record, that does the connection between inner and outer variables
2. Combining that record with #shared (the global environment), which is a record itself
3. Adding that new record as second parameter of Expression.Evaluate()

As you can see this leads to the desired result: 3! But you can shorten the code, by creating the combined environment record inside Expression.Evaluate():

Switching between Global and non global Environments

Imagine the following scenario: You have a query, that is called MyValue, which contains the value 12.

Now you write another query, which contains a variable (step) with the same name MyValue, that has the value 1.

How can you ensure, that the Result of Query1 refers to Query MyValue and not to the step MyValue, or in other words: How can Query1 return the value 12 instead of 1? With environments:

Because the query MyValue is part of the global environment, we can use Expression.Evaluate() and use #shared as environment. That way the query MyValue is evaluated, and not the variable (step) MyValue 🙂

Expression.Evaluate in a calculated column

Let’s say you have the following table and you need to calculate the sum of both columns by using Expression.Evaluate(). This could be the case, when you try to rebuild the example from this blog post from Imke.

The upcoming error message tells us, that the identifier is unknown, or in other words, [Column1] and [Column2] are not part of the environment of the Expression.Evaluate() statement. Inside a table, the underscore „_“ represents the current row, when working with line-by-line operations. The error can be fixed, by adding [_=_] to the environment of the Expression.Evaluate() function. This adds the current row of the table, in which this formula is evaluated, to the environment of the statement, which is evaluated inside the Expression.Evaluate() function.

At the time of writing Expression.Evaluate() is one of two native M functions, which is able to define its own environment. The other one is SqlExpression.ToExpression()…

SqlExpression.ToExpression()

SqlExpression.ToExpression() is no new native M function. It exists for a long time now. Unfortunately this function has an official documentation which only covers the syntax of the function:

SqlExpression.ToExpression(sql as text, environment as record) as text

Honestly we do not know more about this function, but we wanted to name it here, because it can define its own environment. So if you know more about it, feel free to comment below this post 🙂

Quick recap

What did we see so far?

• There is a global environment, which exists (almost always) in parallel to the individual environments of each expression. This is the reason why native M functions and other Power Query queries can be accessed in expressions
• #shared is a variable, returning a record with all the components of the global environment in the current Excel/ Power BI Desktop file
• There exist two native M functions, which can define their own environment: Expression.Evaluate(Expression, optional Environment) and SqlExpression.ToExpression(Sql-Expression, optional Environment)
• Without the optional second parameter, the environment of Expression.Evaluate() is empty. That’s why, unless you define the environment correctly, this function can neither access any variable from inside the Power Query query nor use a native or custom M function.
• The second parameter creates the environment for the defined expressions within the first parameter of the function (e. g. #shared, other variables within the Power Query query, etc.). The second parameter follows the pattern [inner variable = outer variable].
• If you want, that the environment of your Expression.Evaluate() function consists of the global environment ( e. g. to use native m functions) and an outer variable, then you can do that the following way: Use Record.Combine([inner variable = outer variable], #shared) and then use this as second parameter of the Expression.Evaluate() function.
• If you are using Expression.Evaluate() in a calculated column and want to reference columns of the current table, you need to use [_=_] as environment, to pull the current row inside the environment of the Expression.Evaluate() function.

The next post will discuss custom M functions and their environments. Stay tuned 🙂

Regards from Germany,

Lars & Imke

I write my posts for you, the reader. Please take a minute and rate the following 3 categories and press „submit“, to help me write even better posts. Thank you 🙂

[yasr_visitor_multiset setid=2]