Using Variables in Power Query M

In programming, variables allow you to store the result of an expression. Think of them as labeled boxes where you can store values and retrieve them when needed. In the M language, variables work the same way.

Why are Variables Useful?

Variables make your code more readable and easier to manage. Instead of repeating the same value or expression over and over, you can store it in a variable and refer to it by name. This keeps your code clean and easy to understand.

Variables also make debugging easier. When you need to change a value, you can do it in one place instead of searching through your whole code. At the same time, you can return a part of your expression so you can inspects its results. Plus, using variables can improve the performance of your queries by computing a calculation just once and reusing it.

Defining Variables in M

So how do you create variables in Power Query M? The M language has two ways to define variables: using the let in construct and using a record.

The let in Construct

Defining variables in the M language is straightforward. It uses let and in, similar to how DAX uses VAR and RETURN. Here’s how it works:

• let: This keyword starts the variable declaration.
• in: This keyword returns the final result, typically the output of the last defined variable.

Here’s how you can define variables with a let-statement:

let
    a = 5,
    b = a * 2,
    c = b + 3
in
    c

In this example:

• a is defined as 5.
• b is defined as a * 2, which is 10.
• c is defined as b + 3, which is 13.
• The final output is c, so the result is 13.

Using Records to Create Variables

Another way to create variables in M is by using records. A record stores a set of field names and values. And conveniently, you can define your variables as fields in a record:

[ 
  a = 5, 
  b = a * 2, 
  c = b + 3
] 
  [c]

In this example:

• We create a record (by using square brackets) with the field names a, b, and c.
• Each field value is defined similarly to the let in construct.
• The final output is [c], which extracts the value of field c, which is 13.

Let in as Syntax Sugar for Records

The let in construct is syntax sugar for a record. While a let statement creates variables and returns one, a record creates field names and stores values in each field. Whether you use let-in or records, the concept is the same: you’re defining variables and using them to produce a result.

Suppose you want to calculate the total sales after applying a discount. Here’s how you can use variables in both methods:

Using let in:

let
  originalPrice = 100,
  discountRate = 0.1,
  discountAmount = originalPrice * discountRate,
  finalPrice = originalPrice - discountAmount
in
  finalPrice

Using records:

[
  originalPrice = 100,
  discountRate = 0.1,
  discountAmount = originalPrice * discountRate,
  finalPrice = originalPrice - discountAmount
]
  [finalPrice]

Both methods will give you the final price after the discount, which is 90 in this case.

Benefits of Using the Record Construct

Using records for variables offers several advantages over the let-in construct. One big benefit is the ability to return and inspect all values at once. This makes debugging and verifying your code much easier. When you define variables within a record, you can output the entire record and see the results of all your variables at the same time.

For instance, the following record shows the result of each variable:

In this example, the output will be the entire record containing your variables. This shows all the intermediate calculations at once. You can inspect each value to make sure they return the expected results. You can see the original price, discount rate, discount amount, and final price all at once, making it easier to debug and verify your calculations.

However, this is not usually the final output you want. You often use this way of storing variables in a Custom Column. Let’s see how this works.

Using Variables in A Custom Column

When you work with custom columns, consider storing variables in records. Here’s why. When you create a custom column, you often need to perform multiple calculations. Using a record, you can define all your calculations in one place and inspect the result of each one.

To do this, you first define your variables. After confirming the code, you can find record values stored in your newly created column. To inspect them, click on the cell containing the record and see a preview like this:

Once you have verified the results are as expected, you can simply return one of the fields from the record by using a concept called ‘Field Selection’. To do that, you reference the field name within another set of square brackets:

The outcome is a column with the desired end result.

Limitation of the let-in Construct

The let-in construct has a significant limitation: it only allows you to return a single item at a time. This means you can only inspect one variable at a time when debugging your code.

Let’s look at an example:

let
    a = 5,
    b = a * 2,
    c = b + 3
in
    b

In this example, the code returns the value of b. If you want to see the value of a or c, you can’t do it directly within the same let-in construct. You would need to modify the in clause each time to return a different variable, which is cumbersome and inefficient for debugging.

To inspect all variables at once, you have to store them in a more complex structure like a list, table, or record after the in clause, which is manual work and undesired.

Integration with Advanced Editor

Despite the benefits of using records, it’s important to remember that the advanced editor in Power Query primarily supports the let in construct for displaying the ‘Applied Steps’. Therefore, you will often need to use let in statements to maintain the functionality of the advanced editor.

For instance, suppose you add the following code to the advanced editor:

let
  originalPrice = 100,
  discountRate = 0.1,
  discountAmount = originalPrice * discountRate,
  finalPrice = originalPrice - discountAmount
in
  finalPrice

The advanced editor will show each variable making it easy to step through the transformations applied to your data.

This behavior is likely familiar to you because Power Query automatically creates a ‘let’ statement when you perform transformations using the user-interface.

On the other hand, when you store these identical variables in a record and return the result, the power query editor does not recognize record fields as separate steps:

And since one of Power Query strong points is to be able to step through your transformations, I recommend sticking to the let-statement for creating the applied steps. Yet, whenever you need variables within a custom column within your code, consider using a record.

Conclusion

Using the record construct in Power Query M provides a powerful way to inspect all your variable values at once, making debugging and managing custom columns more convenient. However, the let in construct remains essential for maintaining the visibility of ‘Applied Steps’ in the advanced editor. By understanding the benefits and limitations of each approach, you can choose the best method for your situation. This can help to make your queries more efficient and easier to manage.