Forum Discussion
Using variable vs. referencing another query - Excel source
I don't know if there is a performance difference. I never noticed it, but that doesn' t mean anything.
BUT: Talking about concepts: What you call variables, really aren't! Conceptually they are named references to another formula.
M is a functional programming language. It starts at the end en then evaluates all names it encounters as needed. Implying that names not needed, are never evaluated!
Thank you PwerQueryKees!
By reading these pages, I get the impression that each step ("named identifier") in the M code is a separate variable:
M Language basic concepts - PowerQuery M | Microsoft Learn
Power Query M Primer (Part 4): Variables & Identifiers | Ben Gribaudo
So when I was referring to variables, I simply mean a step ("named identifier") in the M code.
Isn't that correct?
I'm interested to learn more about the nuances here 😀
I'm struggling to see the difference between a variable and a step in the M code.
Isn't each step in the M code a variable?
(And I guess each variable can only be accessed locally inside the M query where the variable (i.e. step) was created?
Meaning, I cannot access a variable (step) created inside one M query from another M query?).
Thanks!
Calling them variables suggests that you are storing a value there.
In M the steps are not executed sequentially.
If you execute a query, power query looks at what is after the in keyword. And the it works back.
The names are much more like a function call than a variable. It work a little bit like Excel itself does.
Therefore you can add a name in a query for free, as long as you don't reference it!
And no, you can't use a name inside another query. You can't even use a name inside another let statement within the same query, unless you let statement is 'inside' it.
I know it is a non intuitive way of thinking about queries in power query. And the UI representation as steps doesn't help. Also the preview is misleading as it suggests all steps are always evaluated...
Hope this helps...
Thanks PwerQueryKees ! This is helpful and great food for thought 💡
I know that the let expression works in such a way that it starts with the output - namely, the variable(s) mentioned after the in keyword - and then only evaluates the steps (variables) in the let expression which are required to produce the output (i.e. working it's way backwards when preparing the query execution plan).
However it still seems to me that each step (named identifier) in the let expression is regarded as a variable in the M language (regardless of whether the variable is being used or not):
https://learn.microsoft.com/en-us/powerquery-m/m-spec-basic-concepts#environments-and-variables
"Expressions are evaluated within a given environment. An environment is a set of named values, called variables. Each variable in an environment has a unique name within the environment called an identifier."
"However, let lets us (pun intended) define intermediate expressions whose results are assigned to variables.
Breaking a large expression into intermediate components with names assigned to each makes our code easier to read. Doing this also allows us to reference those intermediate components multiple times as we build toward the let expression’s final return value.
let
Multiplicand = 10,
Multiplier = 20
in
Multiplicand * Multiplier
In the first part of this let expression, variables are defined by name, each followed by an equals sign then the expression producing the value to be assigned to the variable. The variable definitions are separated by commas. The in part of the expression is simply an expression that defines what let returns.
Since the purpose of let is to allow defining variables, it makes sense that at least one of them would be used in the in expression. However, this isn’t mandatory. in can ignore all of the just-defined variables and return something else:
let
Multiplicand = 10,
Multiplier = 20
in
2 + 3"
(Credit to Ben Gribaudo for the above).
Even if the variables assigned in the examples above are just number values, I think the output value of any step in a let expression is regarded as a variable in the M language (i.e. the value assigned to a variable in M can be a table, a list, a record, a json, a binary file, or simply a number, datetime value, text value, etc.). Isn't that correct?
Basically, the way I understand it, all of these named identifiers (highlighted with yellow color) are regarded as variables in M:
Are there any named steps inside a let expression which cannot be regarded as a variable?
Again, I am eager to learn about any nuances here 😀 Thanks!