In this tutorial, you’ll learn about nested expressions, objects, and environments in Power Query. Nested expressions and variables will be used to discuss how Environments work and help generate data. You’ll also learn to deal with common scenarios when transforming nested objects.
Creating Nested Expressions
Expressions are defined as a formula used to construct value.
The expression in the formula bar is built from sub-expressions. The literals 1 and 2 are sub-expressions of the parent expression.
Variables are defined as named values. This is a sample record.
If you open the Advanced Editor window, you can see the variables used in the query.
The variables a, b, and c each represent a value which is the result of an expression you’ll often find after an equal sign. All variables within the parent expression, which is the record itself, form the environment of that record. Within that environment, each variable needs to be unique.
So if you add another variable called c, you’ll see an error message below the Advanced Editor window.
The record can access all of its sub-expressions using identifiers or variable names. However, for nested expressions within the record, each environment is different because they can access all other variables in the record except for themselves.
In this example, you can see that variable c is referencing itself.
If you press done, you’ll get an error message.
If you extract the value of c by adding the item access operator, you’ll get a result. You can also obtain the same result with the let expression because the same rules are applied to it.
Understanding Nested Expressions In An Environment
This is another example of a record value.
If you open the Advanced Editor window, you can see that the variables a and b share the same environment which is the outermost environment. You’ll also see that the variables x, y, and z, are sub-expressions of the parent expression a.
Each variable can access all of the other variables within the nested record except for themselves. You can call a variable that exists in a different environment. In this example, you can see that z is referencing b which is in the outer environment.
Variables x, y, and z can access b because b is part of their parent expression environment. You can also have the same variable but in different environments.
In this other example, you can see that the inner and outer record has a variable x.
Variables need to be unique within their own environment. The closest x assigned to a variable will be used. In this sample query, the inner x is used since it is closer from the perspective of record a.
In this other query, the closest x which is near variable b is used in the expression.
The outer variable b can refer to the variable x inside record a using the item access operator. You need to refer to the variable a and then access x using a set of square brackets. In the same way, record a can refer to the variable x inside variable b.
When the environment of the nested record a is merged with variables from the parent expression, it leads to a conflict because x exists twice and all variables need to be unique in their environment. Since a variable can’t refer to itself, the conflict is resolved by referring to the outer x.
Solving Common Scenarios
A common scenario is transforming nested objects. To demonstrate, this table will be used.
If you want to retrieve a value from the outer table and place it in the nested table, there are two ways to achieve it. The first method is to use the Add Custom Column option.
Click the table icon at the upper left corner of the preview pane and select Add Custom Column.
Input Temp as column name and enter a placeholder in the custom column formula. Once done, press OK.
Inside the formula bar, replace the each expression with a custom function by adding a column to your nested table. Start the logic for a custom function and call it OT for outer table. Then, add the go-to sign.
Next, use the Table.AddColumn function to add a column. Refer the table in the outer table found in the Names column. And then input a new column name.
For this example, the column name used is Revenue. Input the each function to get the values of each row in the Get Revenue column.
If you click the space inside the cell of a row in the Temp column, you’ll get a preview of the nested table below the preview pane. You’ll see that each row has the value of the outer table.
Using Variables To Store Values
The second method of transforming nested objects is by using variables to store values. First, add a custom column and input Temp2 as column name. For the formula, use the let expression to store values in variables.
Input let and then write a variable name. For this example, the variable name is myR. Next, equate the variable with the Get Revenue column. After that, input the in clause and use the Table.AddColumn function to add a column to the nested table.
For the first parameter of the function, input the Names column which contains the nested tables. Next, name the new column Revenue. Use each to call the variables of each row.
If you click the space inside the cell of a row in the Temp2 column, you’ll see that it retrieved the values from the outer table.
Another common scenario is a lookup where there’s no shared key. For this scenario, 2 sample tables will be used: a category table and a product table.
Use the category table as a staging query to compliment the attributes of the product dimension table. Since there’s no shared key, a merge cannot be performed. The first thing to do is nest the full category table in the product table query using the Add Custom Column.
Add a custom column and write Category as column name. Then in the custom formula, call the category query.
After that, you now have nested the full category table in each row of the product table.
When you click the space inside the cell of a row in the Category column, you can see the full category table in each row.
Retrieving A Single Value
If you only want to retrieve a single value, you need to filter the category table down to a single row where a part of the string from the product has a match to the product group in the category table.
Open the Custom Column dialogue box by pressing the gear button beside the Added Custom in the Applied Steps pane.
In the formula, add the Table.SelectRows function. For the first parameter, the Category table is used. The second parameter used the Text.Contains function as a condition. For the inner table, this function checks if a text contains a part from the product string in the outer table.
In accessing the outer table, you’ll notice that Power Query adds the each keyword inside the formula bar. It’s a unary function that takes the underscore ( _ ) as a nameless variable. However, since you are only accessing a column, it can be omitted.
If you click the space inside the cell of a row in the Category column, you’ll now see a single row in each of the nested tables.
From there, you can extract a value for the category with the combination of the row and field access operators.
Go back to the Custom Column dialogue box and add the operators in the formula. To get the first row of the table, input 0 inside a set of curly brackets. Then, list the field name Category inside a set of square brackets.
To future-proof the solution, you can add a question mark (?) in the end to change the not-found behavior from returning an error into a null.
***** Related Links *****
Variables And Expressions Inside Power Query Editor
How The Query Editor Works In Power BI
M Language and Power Query Editor In Power BI
Conclusion
Environments and nested expressions help your query bring out certain values from your codes to provide desirable results. Being aware of them is important when writing functions because when you understand them, you’ll be able to solve more advanced and complex scenarios.
Melissa
