Power Query: Evaluate the Environment
23 June 2021
Welcome to our Power Query blog. This week I look at the importance of environment to Expression.Evaluate().
In last week’s blog I looked at the M function Expression.Constant(); this time I am going to look at a related function Expression.Evaluate()
Expression.Evaluate(document as text, optional environment as nullable record) as any
Returns the result of evaluating an M expression document with the available identifiers that can be referenced defined by environment.
Several examples are given in the help pages, the second goes some way to explaining how to use the function. I have reproduced it here:
The M code is:
= Expression.Evaluate("List.Sum({1, 2, 3})", [List.Sum = List.Sum])
So, the document is a text version of another M function, List.Sum(). The environment is telling the query what to do with the function. I would like to look at what I should put in the environment when I am trying to use this expression.
Since this is a very simple example, I could have just used the document part of the expression.
where the M code is:
List.Sum({1,2,3})
However, there is another way to use the function which is more useful.
Going back to my simple query, I copy the code from the Advanced Editor
and then store it in a text file:
I’ve effectively saved my query.
Now, in a new Blank Query, I am going to use my saved text as a source.
The M code is:
let
Source = Text.FromBinary(File.Contents("C:\Users\kathr\OneDrive\Documents\PQ_StandardExpenses\PQ Blog\evaluate.txt")),
Evaluated = Expression.Evaluate(Source, #shared)
in
Evaluated
This is taking my saved query as text and evaluating what the function should be by using the #shared function.
This executes the stored text.
#shared allows me to access the details of the functions available; it’s like a built-in encyclopaedia of functions.
If I convert this into a table, then I will be able to locate List.Sum().
I just need to filter on Name:
This gives me just that function, so I can drill down to get the details:
I can then view how the function is used:
The #shared function allows Expression.Evaluate() to run the query.
Could I have used this instead of the [List.Sum = List.Sum] in the simple example I started with?
Expression.Evaluate("List.Sum({1, 2, 3})", [List.Sum = List.Sum])
Yes, I could: when using Expression.Evaluate(), using #shared will allow me to set the environment correctly.
Come back next time for more ways to use Power Query!
