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]
What would really be useful is this: Tell Power Query to EVALUATE based on desired source syntax, whether it be Excel, PowerPivot (DAX) or SQL. It would work like automation, but only get the appropriate “formula” engine. Something like Expression.Evaluate(FormulaEvaluator.PowerPivotAddin(“MyDAXFormula”)).
Yes, and similarly it would be useful to have an Excel function that was able to call a DAX query or function, so you could land the output in a single cell in the worksheet.
Chris, can you explain how to use Sections from chapter 11 language specs? I’ve tried everything – does not work.
You can’t use Sections in Power Query. It’s a feature of the language that isn’t exposed, although I think Sections is used somewhere behind the scenes.
Link to language spec not working. Now here: http://pqreference.azurewebsites.net/PowerQueryFormulaLanguageSpecificationAugust2015.pdf
It would be quite interesting if you could reference the outter environment to the one in which Expression.Evaluate is called from.
Because can reference the global environment via #shared/#sections , the current one, but not the one just above. Say if Expression.Evaluate was called within a function and have access to the environment only of the function invocation.
This is absolutely wonderful information. It has enabled me to solve several pressing problems.
Thanks! I now have most of the code in text files on a network drive. Created a tool for all colleagues and if I need to update something, everyones tool is updated as well, since I change the central text files. But, if I want to add a query, I’m lost… Is there a way to create a query from within a query? (using power query within Excel)
Thanks for the post, Chris! Very helpful. Any chance you know of a way to get Applied Steps to display the steps inside a more complex query? If I use Expression.Evaluate to work over multiple steps, I still only get a Source step. I get why that’s happening, but I’m hoping there’s a hack to show the steps of the “inner query.”
Here’s a sample with a simple data table; it only ever shows the Source step since that is the name of the step I’m running:
let
Source = Expression.Evaluate(“let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“”i45WclTSUTI0MFCK1YlWcgKyjaBsZyDbGMSOBQA=””, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
#””Changed Type”” = Table.TransformColumnTypes(Source,{{“”Name””, type text}, {“”Value””, Int64.Type}}),
#””Filtered Rows”” = Table.SelectRows(#””Changed Type””, each ([Name] = “”B””))
in
#””Filtered Rows”””, #shared)
in
Source
You can’t do this, sorry
Shoot. Ah, well. Thanks all the same.
Maybe something along these lines where you would split your expression into a table and evaluate each line. Might be a bit convoluted referencing previous steps but might give you some inspiration.
let
StringToEvaluate = “1+1
2+2
3+3″,
#”Split Text” = Text.Split(StringToEvaluate, “#(cr)#(lf)”),
#”Converted to Table” = Table.FromList(#”Split Text”, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Added Custom” = Table.AddColumn(#”Converted to Table”, “Evaluate”, each Expression.Evaluate([Column1]))
in
#”Added Custom”
I’m trying to return a function. In my case I have some queries with the name ETL_0001, ETL_0002, etc.
I need to return a function in one step. I tried using:
Expression.Evaluate(“ETL_” & Number.ToText( parRecords[ETL_ID], “0000” ) )
but returns error, for example from ETL_0001:
The name ‘ETL_0001’ does not exist in the current context
This is where you need to pass the second parameter to Expression.Evaluate( text, record)…
– so NOT this: Expression.Evaluate(“ETL_” & Number.ToText( parRecords[ETL_ID], “0000” ) )
– but this: Expression.Evaluate(“ETL_” & Number.ToText( parRecords[ETL_ID], “0000” ) , #shared )
This is fantastic! Thank you for sharing. I was just wondering if you knew whether it’s possible to use a text file as M Code to evaluate each row in a table and generate a value. E.g. say I have the following table (simplified here): – apologies for putting it on this post, as it’s similar to the post you were referencing at the top.
(Source table)
ID | AGE
—————
1 | 31
2 | 50
3 | 51
4 | 47
What I am looking to see is whether I can use a txt file that contains the following text (this is the codeLocation.txt file contents)
“[AGE]” = 50
And then in Power query have the following code:
= Table.AddColumn(Source, “TEST”, each let
McodeSourceFile = Text.FromBinary(File.Contents(“C:\codeLocation.txt”)),
EvaluatedExpression = Expression.Evaluate(McodeSourceFile, #shared)
in
EvaluatedExpression)
And produces the following result:
[ID] | [AGE] | TEST
—————
1 | 31 | FALSE
2 | 50 | TRUE
3 | 51 | FALSE
4 | 47 | FALSE
I just don’t think this is doable (to refer to the columns within the query?). Any help or insights would be fantastic!
The whole purpose of this, would be to have a centralised source of code that is embedded in many places so as not to “hardcode” the logic.
Thanks Chris, It can used like INDIRECT function from Excel. You have saved my day.
Thanks Chris, I am using this like INDIRECT function in Excel. You have saved a lot of my time.