A year ago I wrote a post on loading the M code for a Power Query query from a text file using the Expression.Evaluate() function, but I admit that at the time I didn’t understand how it worked properly. I’ve now had a chance to look at this function in more detail and thought it might be a good idea to post a few more examples of how it works to add to what’s in the Library spec.
The docs are clear about Expression.Evaluate does: it takes some text containing an M expression and evaluates that expression, returning the result. The important thing to remember here is that an M expression can be more than just a single line of code – a Power Query query is in fact a single expression, and that’s why I was able to execute one using Expression.Evaluate() in the blog post referenced above.
Here’s a simple example of Expression.Evaluate():
[sourcecode language=”text” padlinenumbers=”true”]
let
Source = Expression.Evaluate("""Hello World""")
in
Source
[/sourcecode]
It returns, of course, the text “Hello World”:
Here’s another example of an expression that returns the number 10:
[sourcecode language=”text”]
let
Source = Expression.Evaluate("6+4")
in
Source
[/sourcecode]
Remember that I said that a Power Query query is itself a single M expression? The way Power Query implements multiple steps in each query is using a let expression, which is a single M expression that contains multiple sub-expressions. Therefore the following example still shows a single expression (consisting of a let expression) being evaluated to return the value 12:
[sourcecode language=”text”]
let
Source = Expression.Evaluate("let a=3, b=4, c=a*b in c")
in
Source
[/sourcecode]
OK, so far not very interesting. What we really want to do is evaluate more complex M expressions.
Consider the following query, which uses the Text.Upper() function and returns the text ABC:
[sourcecode language=”text”]
let
Source = Text.Upper("abc")
in
Source
[/sourcecode]
If you run the following query you’ll get the error “The name ‘Text.Upper’ does not exist in the current context.”:
[sourcecode language=”text”]
let
Source = Expression.Evaluate("Text.Upper(""abc"")")
in
Source
[/sourcecode]
To get a full understanding of what’s going on here, I recommend you read section “3.3 Environments and Variables” of the language specification document, which is available here. The short explanation is that all M expressions are evaluated in an ‘environment’, where other variables and functions exist and can be referenced. The reason we’re getting an error in the query above is that it’s trying to execute the expression in an environment all of its own, where the global library functions aren’t available. We can fix this though quite easily by specifying the global environment (where the global library of functions that Text.Upper() is a part of are available) in the second parameter of Expression.Evaluate() using the #shared intrinsic variable, like so:
[sourcecode language=”text”]
let
Source = Expression.Evaluate("Text.Upper(""abc"")", #shared)
in
Source
[/sourcecode]
#shared returns a record containing all of the names of the variables in scope for the global environment and as such it can be used on its own in a query that returns all of the variables (including all of the functions in the global library and from all other queries in the current workbook) available:
[sourcecode language=”text”]
let
Source = #shared
in
Source
[/sourcecode]
Here’s what that query returns on the workbook that I’m using to write this post, which contains various queries apart from the one above:
Reza Rad has a blog post devoted to this which is worth checking out.
Using #shared will allow you to evaluated expressions that use global library functions but it’s not a magic wand that makes all errors go away. The following query declares a list and then attempts to use Expression.Evaluate() to get the second item in the list:
[sourcecode language=”text”]
let
MyList = {1,2,3,4,5},
GetSecondNumber = Expression.Evaluate("MyList{1}", #shared)
in
GetSecondNumber
[/sourcecode]
Despite the use of #shared in the second parameter we still get the context error we saw before because the variable MyList is still not available. What you need to do here is to define a record in the second parameter of Expression.Evaluate() so that the environment that the expression evaluates in knows what the variable MyList refers to:
[sourcecode language=”text”]
let
MyList = {1,2,3,4,5},
GetSecondNumber = Expression.Evaluate("MyList{1}", [MyList=MyList])
in
GetSecondNumber
[/sourcecode]
This slightly more complex example, which gets the nth item from a list, might make what’s going on here a little clearer:
[sourcecode language=”text”]
let
MyList_Outer = {1,2,3,4,5},
NumberToGet_Outer = 3,
GetNthNumber = Expression.Evaluate("MyList_Inner{NumberToGet_Inner}",
[MyList_Inner=MyList_Outer, NumberToGet_Inner=NumberToGet_Outer ])
in
GetNthNumber
[/sourcecode]
In this example you can see that the two variable names present in the text passed to the first parameter of Expression.Evaluate() are present in the record used in the second parameter, where they are paired up with the two variables from the main query whose values they use.
Finally, how can you pass your own variable names and use functions from the global library? You need to construct a single record containing all the names in #shared plus any others that you need, and you can do that using Record.Combine() to merge a manually created record with the one returned by #shared as shown here:
[sourcecode language=”text”]
let
MyList_Outer = {1,2,3,4,5},
NumberToGet_Outer = 1,
RecordOfVariables =
[MyList_Inner=MyList_Outer, NumberToGet_Inner=NumberToGet_Outer ],
RecordOfVariablesAndGlobals = Record.Combine({RecordOfVariables, #shared}),
GetNthNumber = Expression.Evaluate(
"List.Reverse(MyList_Inner){NumberToGet_Inner}",
RecordOfVariablesAndGlobals )
in
GetNthNumber
[/sourcecode]
Like this:
Like Loading...
