In our last post Imke and I startet to discuss the environment concept in M on the basis of a simple record and its sub-expressions. This current post will go a bit deeper into the topic, looking at environments of nested records and let-expressions. 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.

Environments in single nested records

A nested record is a record within a record. Here you see a record (represented bei the outer square brackets), containing the two variables a and b, while a names an expression, which is a record itself.

The environment for each sub-expression is formed by all the variables belonging to its parent-expression, except itself, and all the variables within the environment of the parent-expression. Let’s make this more plastic: In this example a and b are in the same environment, which is the outer record. Records are structured data, and all variables of the same structural level are in the same environment. The variables x, y and z name the sub-expression of the parent-expression a (the record itself). Each of these variables can access the other variables within their record (as we already know from the last post), except themself. New from here is, that x, y and z can also access b, because b is part of the environment of a, which is the parent-expression of x, y and z.

You can see the result in the following screenshot:

Merging variables in nested records

Now, that you know that sub-expression can access the variables within their parent-expressions environment: What if there is the same variable in different environments. In our last post we explained, that variables can be used as an identifier, because they have to be unique within their environment. But in two different environments, the same variable can exist twice. Take a look at the following nested record:

You see, that variable x is defined twice:

• line 28: within record a (which is correctly addressed a[x]) and
• line 33: definition outside record a

This is a valid definition, as both variables exist in different environments. But what if other variables refer to x? Which x will be used? This is where the knowledge of environments gets handy again. Let’s take a look at it…

Referring to x from inside record a

When x is refered from inside record a, the inner x is used. Inner means, we are looking from the perspective of the record a. Inner and outer always depends on the perspective you have taken. We’re looking from the perspective of record a.

When x is refered in this example, the expression uses the x from inside record a (which is correctly addressed by a[x]), to calculate the value 3:

Referring to x from outside record a

In this example we’re referring to x from outside record a.

This time the expression b = x referes to the outer x. Accordingly, the result is 4:

If b should refere to x from inside record a, instead of the outer x, then the correct expression would look like this:

Referring to the outer x from x inside record a

In this example x is refered from inside record a, again. But this time it is refered from the inner x.

Remember from our first post: Variables are not part of their own environment! This is why you could expect to get an error by the expression x = x. But in this example, it calculates the value 4, instead of  throwing an error: 

How can this behavior be explained?

What technically happens in these examples

When the environments of x, y and z within record a are formed, the following happens: The environment of each variable (x, y and z) gets merged with all the variables existing in the environment of parent-expression a (which is b and x). This leads to a conflict, because when merging these environments, x exists twice, what is not possible. Remember: Variables have to be unique in their environment, because they work as identifiers for their specific expression.

This conflict is resolved in that way, that whenever x is refered, it is always used the „closest“ x:

• When we refered to x from inside record a, a[x] was used
• When we refered to x from outside record a, x was used
• When we refered to x from x inside record a (a[x] = x), another rule kicked in (read about it here), which says, that a variable is not part of its own environment, so that the outer x is the closest x.

In addition to record, also let expression create their own environment. Let’s have a look at this topic too.

Environments in let-expressions

Let expressions define one or more expressions after the let, and return a value, which is calculated by the expression after the in. Regarding environments, the only important information for you is, that the expression after the in, can access all the variables defined in the let expression, because they belong to its environment.

Quick recap

What did we see so far?

• Variables can not only access other variables within their environment, but also all variables within the environment of their parent-expression (record a)
• All these variables are merged, to form the environment of the specific variable.
• This can lead to conflicts, as variables can occur multiple times in different environments.
• This conflict is resolved by always using the variable, that is closest to the expression, that refers to it.
• Let expressions: The expressions after the in can access all the variables defined in the let expression, because they belong to its environment.

The next post will discuss native M functions, which can define their own environment. Stay tuned 🙂

Regards from Germany,

Lars & Imke