The environment concept (in our opinion) is one of the most important concepts in M, while it is one of the least obvious ones. This series of posts is written in collaboration with Imke Feldmann from thebiccountant.com. Our motivation for this series is, to make this concept more popular and to show where it is needed to overcome some aparrently strange error messages. We’ll be very close to the official language specification, which you can find here. Because the official documentation is a very technical one, we’ll try to write it more understandable and add examples, where it adds value. You, as a reader, are highly encouraged to give feedback and suggesttions. We’d love to have a great discussion about environments in M. 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.
Before we start with environments: Expressions and variables
Expressions and variables are two other concepts, which build the basis to understand environments.
Expressions are defined as follows: An expression is a formula used to construct values. Here are some examples:
While the example 123 consists of only a single number, the expression 1 + 2 is built from so called sub-expressions. The literals 1 and 2 are sub-expressions of the parent expression 1 + 2.
The parent- / sub-expression thing is not always very obvious. When creating the record [ x = 1, y = 2 + 3], the record itself is the parent expression, while 1 and 2 + 3 are sub-expressions of the record.
To understand variables, take a look at the definition of the following record:
Variables are named values. The variables in this record are x, y and z. They name the values, which are the result of evaluating the expression behind the equal signs. All these variables within the parent expression (which is the record itself) form the environment of the record. Within this environment each variable has to be unique. If you try to define a variables twice, within this record, the following message appears:
Because each variable is unique within a certain environment, it works as an identifier within this environment, that can me referenced. We will be discussing this later throughout this post.
Environments in M
Understanding the concept of environments is crucial, to understand calculations and the occurrence of error messages in M. As mentioned before, the record builds its own environment, consisting of all the variables in the record.
For the following record this means, that the environment of the record consists of all the variables x, y and z:
This means, that the record itself can access all its sub-expressions by using their identifiers (variables). This leads to 6 as the result of the following expression:
For the expressions within the record, the environments are a slightly different thing:
First of all take a look at line 28. Because the variables are unique within their environment, they work like identifiers, which can be referenced. This is why z = x + y can be evaluated at all. But also take a look at the comments that show information about the different environments for each variable: Each variable within the record, can access all the other variables, except itself. Ignoring this, leads to the following error message:
Because z is not part of its own environment, it can’t evaluate the expression z + y. Please read the error message precisely: The variable z is simply not existing in the environment of z = z + y.
What did we see so far?
- Expressions produce values. They can consist of literals (like 1), or of more complex sub-expressions (like 1 + 2).
- Variables name those expressions (like a = 1 + 2).
- Variables are unique within their environment. This is why they can be used as identifiers.
- Parent expressions build their environment from all the variables, naming their sub-expressions. Sub-expressions can be evaluated in a different environment.
- Ignoring these concepts can result in error messages, which usually are not self-explanatory.
The next post will discuss environments in nested records and let-expressions. Stay tuned 🙂
Regards from Germany,
Lars & Imke
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…