This tutorial will discuss about variables and expressions inside the Power Query Editor. You’ll see and learn that each let expression has two parts: a let and in clause, and a comma-separated list of variable names or step identifiers. You’ll also see how these things help create a data report in the Power Query Editor.
Understanding Let Expressions And M Language
The Let expression allows you to string a series of expressions together into a single and more complex expression. Variables are then used to store values of any type like numbers, texts, dates, or structure data types such as records, lists, or tables.
Most queries return a table that can be used as a staging query, or that can be loaded to the data model. An icon beside a query name shows the value type that the query returns.
Every query in the query editor is a single M expression. When that query runs, the expression is evaluated and then a value is returned. The value is determined by whatever follows in the in clause. The in clause can refer to any or none of the variables in the variable list. It can even refer to another query or contain an expression.
To add a new query, right-click on the blank space in the Query pane on the left side of the screen. Then, click New Query and select Blank Query.
Open the Advanced Editor window by clicking Advanced Editor in the Query section.
You’ll notice that the new query isn’t blank.
It starts with a let clause and followed by a single variable source. That variable has a literal assigned to it, which is the double quote signs that represent an empty text string. It also has the in clause followed by the source variable name.
If you look at the query pane, you’ll see an ABC icon before the query name which tells you that the query returns a text value.
Using Simple Variables And Expressions
For this example, create a simple syntax in the Advanced Editor window by entering A as your variable and use the equal sign to assign a value or an expression to the variable.
To create or add a new variable, put a comma at the end of the first variable then press Enter. On the next line, you can input a new variable.
Aside from assigning values and expressions, you can also assign variables in the variable list.
If you’ve entered your last variable, you don’t need to put a comma in the end. Just press Enter and input the in clause. For this example, input C after the in clause to return the value of C.
If you press Done, you’ll get a numeric value of 6, which is the result of the expression in the formula bar.
The result is a numeric value because the icon beside the query name is replaced by a 123 icon, which represents the number data type.
Naming Variables And Step Identifiers
If your variable names contain spaces, they need to be enclosed in double-quotes and have a hash or pound sign in front of it. Double quotes are reserved for text values.
To differentiate between the hash or pound sign, rename the steps in the Applied Steps pane on the right side of your screen. Right-click on B, select Rename, and then name it var B.
After that, go back to the Advanced Editor window. You’ll see that Power Query automatically renames B to var B in all the places that it was used or referenced.
This is only available when you rename the step in the Applied Steps pane. If you rename a step in the Advanced Editor window, you also have to manually update all the places where that variable was referenced. There’s no find and replace option in the Advanced Editor window.
Now, for this example, don’t forget to change the variable name back to B and click Done.
Recreating Variables And Expressions In Power BI Desktop
The structure of the code inside the Advanced Editor window is similar to how you would write it in DAX.
Try and compare both of them. Go to the Home tab and select Close & Apply. Once you’re in the Power BI desktop, go to the Home tab and select Enter data to create a measure.
Click Load on the Create Table pop-up.
Then, right-click on the Table in the Fields pane and select New measure.
In the formula bar, recreate the same code from the M language. To initiate a variable index, you have to use the VAR statement. Following that, you can then write your variables and their values. In DAX, you need to use the RETURN statement to access any of the variables. So, input RETURN after the last variable and input C to return the results of its expression.
Next, add a card visualization and select the measure that you created. You’ll then see the same numeric value as the M result.
If you change the order of the variables in the formula bar, an error will occur in the card.
Order is important when dealing with DAX variables. You can’t call a variable that hasn’t been declared yet.
However, if you change the order in the M code, you’ll still get the result.
The expression states that in order to evaluate C, the values for the variables A and B must be known. This is an example of dependency ordering.
The M engine will follow the dependency ordering provided by expressions, but it’s free to perform all remaining calculations in any order it chooses.
Understanding M Queries And Codes
All M queries look like procedural codes because you are using the user interface to generate the code for you. It will always look sequential because each transformation step builds on the value that’s returned by the previous step.
The main thing that’s important to the M engine is the dependency chain that can be followed back from the in clause. The Query Engine will evaluate a query from the end backwards and not from the beginning forwards.
Why does the engine do that? First, when the query gets evaluated, it looks back and tries to eliminate anything that’s unnecessary. Values that aren’t used won’t be evaluated but ignored. This is a process called Lazy Evaluation.
Second, it tries to look at all the transformations you’re performing and push as much of that work back to the source system. This process is called Query Folding. Both processes play a key role in improving query performance.
The final thing to remember is that most of the time you can see all your variable names or step identifiers in the Applied Steps pane. When M code is written out of order, the expressions will appear as a single combined step like this:
You can easily fix that by reordering the steps. Place the variable C back at the bottom of variable B and then click Done. You’ll then see the step names appear in the Applied Steps pane again.
Conclusion
***** Related Links *****
DAX Queries Using Variables And Important Functions
Power BI Formula Syntax, Comments, And Variables
Using Variables In Power BI – A Detailed Example
Variables and expressions are some of the most important things to understand inside the Power Query Editor. Both are responsible for generating data and information in your report. They gather different data in order to obtain and produce results.
Melissa
