Expression.Evaluate - Expression Function

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 any

Description

Expression.Evaluate executes an M expression provided as text and evaluate the result as if it was code. This allows you to dynamically evaluate code. It can interpret mathematical operations, M functions, and custom logic, with an optional environment to define how identifiers are processed.

Examples

To better understand Expression.Evaluate, we’ll to dive into a series of examples.

Basic Mathematical Operations

Imagine you have a text value containing a simple mathematical operation. Typically, using a text value would return a text result. But what if you want the engine to compute the mathematical operation within your text? That’s where Expression.Evaluate can help.

Consider this line of code:

Expression.Evaluate( "5 + 10" ) // Output: 15

Here, the text "5 + 10" is interpreted as a mathematical operation, and the calculation is performed accordingly.

Handling M Language Functions

Expression.Evaluate 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: 6

The second argument here—an optional environment—instructs 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: 6

Custom Logic with Optional Environment

The 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"""] 
)

We can also tell Expression.Evaluate to treat ‘Groot’ differently:

// Output: "RickDeGorilla"
Expression.Evaluate( 
  Expression.Constant( "RickDe" ) 
  & "&" 
  & "Groot", 
  [Groot = "Gorilla"] 
)

This expression treats the string “Groot” as if we input the text “Gorilla”. Here’s an alternative that 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.

Executing M Code from a GitHub Raw Link

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
    EvaluateFunction

The 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.

Executing M Code from a Local Text File

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( BinaryText, #shared )
in
  EvaluateFunction

To conclude, with Expression.Evaluate you gain a new dimension of flexibility in Power Query M.

Other functions related to Expression.Evaluate are:

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/expression-evaluate