Expression.Evaluate is a Power Query M function that evaluates an M expression document with the available identifiers defined by the environment. The function returns the result of the evaluation.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Expression.Evaluate(
document as text,
optional environment as nullable record,
) as anyDescription
Returns the result of evaluating an M expression document, with the available identifiers that can be referenced defined by environment.
Examples
To truly understand Expression.Evaluate, we’re going to dive into a series of practical examples.
Picture this: you have a text value containing a simple mathematical operation. Typically, introducing text to Power Query would yield a text result. But what if you want the engine to compute the mathematical operation within your text? That’s where Expression.Evaluate is useful.
Consider this line of code:
Expression.Evaluate( "5 + 10" ) // Output: 15What’s happening here? The text “5 + 10” is interpreted as a mathematical operation, and the calculation is performed accordingly.
But there’s more. The Expression.Evaluate function can even handle Power Query M language functions. For instance, let’s say you want to use the List.Sum function
Expression.Evaluate(
"List.Sum( { 1, 2, 3 } )",
[List.Sum = List.Sum] // Optional environment
) // Output: 6The second argument here—an optional environment—directs Power Query on how to process identifiers. Although optional, omitting the environment in this scenario results in an error, stating that ‘List.Sum’ doesn’t exist in the current context: “Expression.Error: [1,1-1,9] The name ‘List.Sum’ doesn’t exist in the current context.“
Power Query needs guidance to comprehend the text ‘List.Sum’. To achieve the same outcome, we can also use #shared to specify the global environment:
Expression.Evaluate(
"List.Sum( { 1, 2, 3 } )",
#shared // specifies the global environment
) // Output: 6The optional environment argument can also be a playground for your custom logic. Let’s examine an example where you’d want to concatenate values:
// Expression evaluates without using the environment. Output: "RickDeGroot"
Expression.Evaluate( Expression.Constant( "RickDeGroot" ), [Groot = "Gorilla"""] )
// Expression treats string "Groot" as if it's "Gorilla". Output: RickDeGorilla
Expression.Evaluate(
Expression.Constant( "RickDe" )
& "&"
& "Groot",
[Groot = "Gorilla"]
)
// Expression treats "Rick" as "Groot" and "Groot" as"Gorilla". Output: GrootDeGorilla
Expression.Evaluate(
"Rick" & "&"
& Expression.Constant( "De" )
& "&"
& "Groot",
[Rick = "Groot", Groot = "Gorilla"]
)That shows you can use the environment to specify “variables”. And those “variables” then replace any occurrences of that value in the text.
Let’s explore some advanced uses of Expression.Evaluate, specifically, executing M code from a GitHub raw link or a text file. Imagine a scenario where you need to run M scripts stored on a GitHub repository directly from Power Query. Here’s how you do it:
let
Source =
Web.Contents("https://raw.githubusercontent.com/Softwaretrain/M-Functions/main/J_Diff"),
BinaryText =
Text.FromBinary( Source ),
EvaluateFunction =
Expression.Evaluate( BinaryText, #shared )
in
EvaluateFunctionThe code begins by connecting to the repository URL via the Web.Contents function, returning a binary value. To extract the underlying text (which contains the M code), we use Text.FromBinary. Now, our M script is a text value.
We then invoke Expression.Evaluate to execute the M script. The ‘#shared’ as the environment argument allows all globally defined identifiers to be available to the evaluated expression—essentially guiding Power Query to recognize and process known Power Query functions in the text.
Following a similar line of thought, you can execute M code stored in a local text file:
let
Source = File.Contents( "C:fn_PersianDateToPersianTextDate.txt" ),
BinaryText = Text.FromBinary( Source ),
EvaluateFunction = Expression.Evaluate( Source, #shared )
in
EvaluateFunctionTo conclude, with Expression.Evaluate at your disposal, you gain a new dimension of flexibility in Power Query M.
Related functions
Other functions related to Expression.Evaluate are:
