The Let Expression in Power Query M

The let expression in Power Query is one of the first building blocks to understand if you want to master the M language. It’s used in the advanced editor and makes it possible to define variables (intermediate steps) and specify which result you want to return in the final output. This structure organizes your queries and makes troubleshooting and adjusting results easy. So how does it work?

What is a Let Statement?

In Power Query, a let statement is an important construct for defining the different steps that make up your query. Using let, you can create named steps, or “variables,” and manage how Power Query performs each calculation.

A let-expression always starts with the let keyword, followed by variable definitions (each separated by a comma), and ends with the in keyword followed by the variable to return.

Here’s an example of what a let-expression looks like:

let
    firstValue = 10,
    secondValue = firstValue * 2,
    thirdValue = secondValue + 7,
    fourthValue = thirdValue - 2
in
    fourthValue

The code example defines four variables where you can find the names before the equal sign (=), followed by an expression. In this example, the second, third, and fourth variables all refer to an earlier step in their expression. Finally, the fourthValue variable is returned indicated after the in keyword.

Comparing the LET expression with DAX

If you’re familiar with DAX, you might recognize a similar structure with the VAR and RETURN keywords. Both DAX and Power Query have a set of keywords to define variables and return a result.

In DAX, you use the VAR keyword to define each variable and the RETURN keyword to specify which variable should be displayed in the result. We can write the above LET-expression in DAX using the following code:

VAR firstValue = 10,
VAR secondValue = firstValue * 2,
VAR thirdValue = secondValue + 7,
VAR fourthValue = thirdValue - 2
RETURN
    fourthValue

As you can see, defining variables in DAX is very similar to M. The key differences are:

• Syntax: DAX uses VAR for each variable and RETURN for the result, while Power Query uses let to start the variable section and in to specify the final output.

• Return: In DAX, the variable to return comes after the RETURN keyword, whereas in M it follows the in keyword.

Simplified vs. Fully Qualified Notations

When working with variables in Power Query, knowing the rules for naming or “identifying” these variables is important. Power Query allows two types of notations for identifiers: a simplified notation and a fully qualified notation. Let’s see when each is used and how they differ.

Simplified Notation

The simplified notation is the most straightforward way to name a variable in Power Query. The M language supports the simplified notation if your variable meets the following requirements:

1. No Spaces: Variable names should be a single word (e.g., firstValue).
2. No Special Characters: Besides underscores, avoid characters like #, %, &, @, or any other symbols.
3. Does Not Start with a Number: The variable should begin with a letter or an underscore. Starting with a number is not allowed.

For instance, our earlier example uses the simplified notation:

let
    firstValue = 10,
    secondValue = firstValue * 2,
    thirdValue = secondValue + 7,
    fourthValue = thirdValue - 2
in
    fourthValue

Here, variable names consist of only their names without quotations or the # symbol.

Fully Qualified Notation

When a variable name includes spaces, special characters (besides underscores), or begins with a number, Power Query requires a fully qualified notation. This notation uses double quotation marks around the variable name, preceded by a # symbol.

Let’s look at how this works in code:

let
    #"5firstValue" = 10,
    #"secondValue@" = #"5firstValue" * 2,
    #"third!Value" = #"secondValue@" + 7,
    _fourthValue = #"third!Value" - 2
in
    _fourthValue

Notice the # symbol and quotation marks around each variable name that doesn’t meet the simplified requirements. This notation tells Power Query to interpret these names precisely as written, with spaces and symbols intact.

Why is this important? Well, Power Query requires the correct use of notations. If you forget to use the fully qualified notation for a name with spaces or special characters, it will return an error. Besides, the simplified approach makes your code easier to read and understand.

Where to Find the Let expression in Power Query

When you use Power Query’s interface, every action you perform—like loading a table or filtering rows— automatically creates a step. These steps use the let statement under the hood. You can view and edit the let structure directly by opening the Advanced Editor.

To find the let statement:

1. In Power Query, open the query you want to edit.
2. Go to the Home tab and select Advanced Editor.

Here, you’ll see the let expression at the top, showing all the steps Power Query has created for you. The let statement in this editor shows each step as a variable, helping you understand and edit the logic behind your query.