Implementing Custom Expression.Evaluate Pipelines in Power Query M: Dynamic Code Generation, Runtime Formula Injection, and Safe Sandboxed Execution Patterns

Introduction

Here's a scenario that comes up more often than you'd expect: your business users maintain a configuration table in SharePoint. Each row represents a business rule — a formula that determines whether a transaction should be flagged, a calculated KPI, or a dynamic filter condition. The rules change frequently, and nobody wants to open Power BI Desktop every time a formula tweaks. What you need is a system that reads those formula strings at runtime and actually executes them against your data.

This is exactly what Expression.Evaluate enables. It's one of the most powerful — and most misunderstood — functions in the entire M standard library. It lets you take a string containing valid M code and evaluate it at runtime, with full access to whatever environment you provide. The moment you understand this function deeply, entire categories of problems that seemed impossible suddenly have elegant solutions: user-configurable transformations, self-describing data pipelines, meta-driven ETL systems, and dynamic aggregation engines.

By the end of this lesson, you'll have moved well beyond the toy examples you'll find scattered around the internet. You'll understand how Expression.Evaluate actually works inside the M runtime, how to construct safe and robust execution environments, how to generate M code programmatically, how to handle errors gracefully, and how to design the kind of pipeline architecture that can handle real enterprise requirements without becoming a maintenance nightmare.

What you'll learn:

• How Expression.Evaluate works under the hood and what its environment parameter actually controls
• How to construct scoped execution environments using #shared and custom record types
• Techniques for generating valid M code strings dynamically from configuration tables
• Sandboxing strategies to limit what evaluated expressions can access or do
• Error handling patterns that catch malformed formulas without crashing your entire pipeline
• Performance implications and when Expression.Evaluate becomes a bottleneck

---

Prerequisites

You should be comfortable with M as a language before diving into this material. Specifically, you'll want solid footing in M language fundamentals including syntax, types, and expressions, and you should understand how custom M functions work because Expression.Evaluate returns function values as first-class citizens. Familiarity with error handling patterns using try-otherwise is also essential — you'll need it to build resilient evaluation pipelines.

---

How Expression.Evaluate Actually Works

Let's start with the basics and immediately go deeper than most tutorials bother to go.

The function signature is:

Expression.Evaluate(document as text, optional environment as record) as any

The document parameter is a string containing a complete M expression. The environment parameter is a record whose fields define what names are in scope when that expression is evaluated.

Here's the critical insight that most explanations miss: M expressions do not have implicit access to your query environment. When you write = 1 + 1 in a query step, the M engine knows about all the other queries and parameters in your file. But when Expression.Evaluate runs your string, it starts with a completely blank slate unless you explicitly provide an environment. This is both the feature and the footgun.

// This will FAIL because Number.Add is not in scope
Expression.Evaluate("Number.Add(1, 2)")

// This works because we provided the environment
Expression.Evaluate("Number.Add(1, 2)", [Number.Add = Number.Add])

// This works because #shared provides ALL standard library functions
Expression.Evaluate("Number.Add(1, 2)", #shared)

The #shared intrinsic is a record containing every function in the M standard library. Using it as your environment gives evaluated expressions access to the full M function library — which is usually what you want, but has security implications we'll discuss shortly.

Let's look at what the function can actually return. Because M is an expression-oriented language, any valid M expression is fair game:

// Returns a scalar
Expression.Evaluate("42", #shared)                          // => 42

// Returns a list
Expression.Evaluate("{1, 2, 3}", #shared)                   // => {1, 2, 3}

// Returns a function!
Expression.Evaluate("(x) => x * x", #shared)               // => a function value

// Returns a table expression result
Expression.Evaluate(
    "#table({""Name"", ""Value""}, {{""A"", 1}, {""B"", 2}})",
    #shared
)                                                           // => a two-row table

That last capability — returning a function — is what makes Expression.Evaluate genuinely useful for meta-programming. You can build a function factory that generates M functions from string templates.

---

Constructing Execution Environments

The environment record is where the real architectural design happens. Your choice of environment determines what the evaluated expression can see, what it can call, and what data it can access. This decision has correctness, security, and performance dimensions.

Using #shared as a Base

The simplest approach is #shared, which surfaces the full standard library. You can extend it by merging in additional bindings:

let
    // Define a reusable context value
    SalesData = Table.FromRecords({
        [Region = "North", Revenue = 450000, Units = 1200],
        [Region = "South", Revenue = 380000, Units = 950],
        [Region = "East",  Revenue = 510000, Units = 1400]
    }),
    
    // Build an environment that includes standard library + our data
    EvalEnvironment = Record.Combine({
        #shared,
        [SalesData = SalesData]
    }),
    
    // Now evaluated expressions can reference SalesData by name
    Result = Expression.Evaluate(
        "Table.SelectRows(SalesData, each [Revenue] > 400000)",
        EvalEnvironment
    )
in
    Result

Record.Combine merges multiple records into one, with later records taking precedence on field name collisions. This pattern of Record.Combine({#shared, customBindings}) is the standard foundation for most evaluation environments.

Building a Minimal Environment

Sometimes you want the opposite: a minimal environment that gives the evaluated expression access to only what you explicitly allow. This is the sandboxing approach, and it's the right choice when:

1. The formula strings come from user input or external configuration
2. You don't want evaluated code to be able to call Web.Contents, File.Contents, or any other data-access function
3. You're building a reusable component and want deterministic, auditable behavior

let
    // A safe math-and-logic environment, no I/O, no data connectors
    SafeMathEnv = [
        // Arithmetic
        Number.Add      = Number.Add,
        Number.Subtract = Number.Subtract,
        Number.Multiply = Number.Multiply,
        Number.Divide   = Number.Divide,
        Number.Abs      = Number.Abs,
        Number.Round    = Number.Round,
        Number.Power    = Number.Power,
        Number.Sqrt     = Number.Sqrt,
        
        // Comparison and logic  
        Number.IsNaN    = Number.IsNaN,
        Logical.And     = (a, b) => a and b,
        Logical.Or      = (a, b) => a or b,
        Logical.Not     = (x) => not x,
        
        // Text basics
        Text.Upper      = Text.Upper,
        Text.Lower      = Text.Lower,
        Text.Length     = Text.Length,
        Text.Contains   = Text.Contains,
        
        // Type checking
        Value.Is        = Value.Is,
        Value.Type      = Value.Type,
        
        // Conditionals (these are keywords, not functions, so you use wrapper funcs)
        If              = (condition, trueVal, falseVal) => 
                             if condition then trueVal else falseVal
    ],
    
    // Test it
    Score = 87.5,
    Env   = Record.Combine({SafeMathEnv, [Score = Score]}),
    
    Result = Expression.Evaluate(
        "if Score >= 90 then ""A"" else if Score >= 80 then ""B"" else ""C""",
        Env
    )
in
    Result

Environment Scoping Patterns

A more sophisticated pattern involves building a layered environment system where different expression types get different capability levels:

let
    // Layer 1: Pure computation, no data access
    Layer_Compute = [
        Number.Round    = Number.Round,
        Number.Abs      = Number.Abs,
        Number.Power    = Number.Power,
        Text.Format     = Text.Format,
        Date.From       = Date.From,
        Duration.Days   = Duration.Days
    ],
    
    // Layer 2: Data manipulation (table/list functions but no I/O)
    Layer_Transform = Record.Combine({
        Layer_Compute,
        [
            Table.SelectRows    = Table.SelectRows,
            Table.TransformColumns = Table.TransformColumns,
            Table.AddColumn     = Table.AddColumn,
            Table.Group         = Table.Group,
            List.Sum            = List.Sum,
            List.Average        = List.Average,
            List.Max            = List.Max,
            List.Min            = List.Min,
            Record.Field        = Record.Field
        ]
    }),
    
    // Layer 3: Full standard library (use with trusted code only)
    Layer_Full = #shared,
    
    // Select environment by trust level
    GetEnvironment = (trustLevel as text) as record =>
        if trustLevel = "compute" then Layer_Compute
        else if trustLevel = "transform" then Layer_Transform
        else if trustLevel = "full" then Layer_Full
        else error "Unknown trust level: " & trustLevel
in
    GetEnvironment

You would call this function factory once and then reuse the returned environments across many Expression.Evaluate calls, which is important for performance since building these environment records isn't free.

---

Dynamic Code Generation Patterns

The other half of this capability is generating valid M code strings programmatically. This is where things get genuinely powerful — and where bugs get genuinely subtle.

The Code Generation Mindset

Think of code generation as template rendering with strict escaping rules. M has a small set of syntax rules you need to respect:

1. String literals must use double quotes, and internal double quotes must be doubled: "He said ""hello"""
2. Identifiers that contain spaces or special characters must be wrapped in #"..." syntax
3. Number literals use period as decimal separator regardless of locale
4. Null is a keyword, not a function call

Let's build a practical code generator. Suppose you have a configuration table that defines computed columns to add to a sales table:

ColumnName	Formula
GrossMargin	Revenue - Cost
MarginPct	(Revenue - Cost) / Revenue * 100
PriorYearDiff	Revenue - PriorYearRevenue
IsHighValue	Revenue > 500000

You want to programmatically generate the M code that adds all these columns in sequence.

let
    // Configuration table (in practice, loaded from SharePoint or a parameter table)
    ColumnConfig = Table.FromRecords({
        [ColumnName = "GrossMargin",    Formula = "[Revenue] - [Cost]"],
        [ColumnName = "MarginPct",      Formula = "([Revenue] - [Cost]) / [Revenue] * 100"],
        [ColumnName = "PriorYearDiff",  Formula = "[Revenue] - [PriorYearRevenue]"],
        [ColumnName = "IsHighValue",    Formula = "[Revenue] > 500000"]
    }),
    
    // Source data
    SalesData = Table.FromRecords({
        [Region = "North", Revenue = 450000, Cost = 320000, PriorYearRevenue = 410000],
        [Region = "South", Revenue = 380000, Cost = 290000, PriorYearRevenue = 395000],
        [Region = "East",  Revenue = 510000, Cost = 360000, PriorYearRevenue = 480000]
    }),
    
    // Code generator: produces a single chained expression that adds all columns
    GenerateAddColumnsCode = (configRows as list, sourceTableName as text) as text =>
        let
            // For each config row, generate one Table.AddColumn call
            // We build from the inside out (innermost = last column applied first)
            GenerateSingleStep = (colName as text, formula as text, innerExpr as text) as text =>
                "Table.AddColumn(" & innerExpr & ", """ & colName & """, each " & formula & ")",
            
            // Fold: start with the source table name, wrap each column around it
            FinalCode = List.Accumulate(
                configRows,
                sourceTableName,
                (state, row) => GenerateSingleStep(
                    Record.Field(row, "ColumnName"),
                    Record.Field(row, "Formula"),
                    state
                )
            )
        in
            FinalCode,
    
    // Convert config table to list of records
    ConfigList = Table.ToRecords(ColumnConfig),
    
    // Generate the M code string
    GeneratedCode = GenerateAddColumnsCode(ConfigList, "SourceTable"),
    
    // Build environment with source data injected
    EvalEnv = Record.Combine({#shared, [SourceTable = SalesData]}),
    
    // Execute it
    Result = Expression.Evaluate(GeneratedCode, EvalEnv)
in
    Result

The generated code string will look like this (formatted for readability):

Table.AddColumn(
    Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(
                SourceTable, 
                "GrossMargin", 
                each [Revenue] - [Cost]
            ), 
            "MarginPct", 
            each ([Revenue] - [Cost]) / [Revenue] * 100
        ), 
        "PriorYearDiff", 
        each [Revenue] - [PriorYearRevenue]
    ), 
    "IsHighValue", 
    each [Revenue] > 500000
)

This is valid M that the evaluator can parse and execute. The nesting represents the dependency chain — each outer Table.AddColumn call operates on the result of the inner one.

Handling String Escaping Properly

String escaping is where code generation goes wrong most often. If your formula or column name contains double quotes, your generated code will be syntactically invalid. You need an escaping helper:

let
    // Escape a string for safe embedding in M code
    EscapeForMString = (s as text) as text =>
        Text.Replace(s, """", """"""),
    
    // Wrap an identifier that might contain spaces
    SafeIdentifier = (name as text) as text =>
        if Text.Contains(name, " ") or Text.Contains(name, ".") 
        then "#""" & EscapeForMString(name) & """"
        else name,
    
    // Build a safe column reference (for use in `each` expressions)
    SafeColumnRef = (colName as text) as text =>
        "[" & EscapeForMString(colName) & "]",
    
    // Test cases
    NormalCol  = SafeColumnRef("Revenue"),            // => [Revenue]
    QuotedCol  = SafeColumnRef("Q1 ""Adjusted"""),    // => [Q1 ""Adjusted""]  
    SpacedId   = SafeIdentifier("My Config Table"),   // => #"My Config Table"
    NormalId   = SafeIdentifier("SalesData")          // => SalesData
in
    [NormalCol = NormalCol, QuotedCol = QuotedCol, 
     SpacedId = SpacedId, NormalId = NormalId]

For column names with spaces, use the #"..." identifier syntax in your generated code. The M parser handles this correctly and it's the canonical way to reference such names.

Generating let Expressions

For more complex scenarios, you might want to generate full let...in expressions rather than single-expression calls. This is necessary when you need intermediate variables in your generated code:

let
    GenerateLetExpression = (steps as list, finalStep as text) as text =>
        let
            // steps is a list of records: [Name, Expression]
            StepLines = List.Transform(
                steps,
                each "    " & Record.Field(_, "Name") & " = " & Record.Field(_, "Expression")
            ),
            StepBlock  = Text.Combine(StepLines, "," & "#(lf)"),
            LetBlock   = "let" & "#(lf)" & StepBlock,
            InBlock    = "#(lf)in" & "#(lf)    " & finalStep,
            FullCode   = LetBlock & InBlock
        in
            FullCode,
    
    // Build a computation with intermediates
    GeneratedCode = GenerateLetExpression(
        {
            [Name = "BaseTable",    Expression = "SourceTable"],
            [Name = "Filtered",     Expression = "Table.SelectRows(BaseTable, each [Revenue] > 400000)"],
            [Name = "WithMargin",   Expression = "Table.AddColumn(Filtered, ""Margin"", each [Revenue] - [Cost])"]
        },
        "WithMargin"
    )
in
    GeneratedCode

The #(lf) is M's character escape for a line feed. This produces a properly formatted let...in block that reads cleanly when you inspect it.

---

Safe Sandboxed Execution Patterns

Security in the context of Expression.Evaluate isn't about preventing SQL injection — it's about controlling what an evaluated formula can do. The primary threats are:

1. Data exfiltration: An expression calls Web.Contents and sends your data somewhere
2. Infinite loops: An expression contains a recursive structure that never terminates
3. Resource exhaustion: An expression generates enormous lists or tables in memory
4. Side effects: An expression triggers file writes or external API calls

The good news is that M is a fundamentally pure, functional language with lazy evaluation. There are no mutable state mechanisms, no file system write functions in the standard library, and the execution engine has its own timeout and memory limits. But you should still design defensively.

The Whitelist Approach

The most reliable sandbox is an explicit whitelist — you only include functions you've deliberately chosen. This is the minimal environment approach from earlier, but with a systematic methodology:

let
    // Approved function lists by category
    ApprovedMathFunctions = {
        "Number.Abs", "Number.Add", "Number.Divide", "Number.Multiply",
        "Number.Round", "Number.RoundDown", "Number.RoundUp",
        "Number.Sqrt", "Number.Power", "Number.Sign",
        "Number.IsNaN", "Number.IsOdd", "Number.IsEven",
        "Int8.From", "Int16.From", "Int32.From", "Int64.From",
        "Double.From", "Decimal.From", "Currency.From"
    },
    
    ApprovedTextFunctions = {
        "Text.Upper", "Text.Lower", "Text.Proper",
        "Text.Length", "Text.Start", "Text.End", "Text.Middle",
        "Text.Contains", "Text.StartsWith", "Text.EndsWith",
        "Text.Replace", "Text.Trim", "Text.TrimStart", "Text.TrimEnd",
        "Text.Split", "Text.Combine", "Text.PadStart", "Text.PadEnd",
        "Text.From", "Text.Format"
    },
    
    ApprovedDateFunctions = {
        "Date.From", "Date.Year", "Date.Month", "Date.Day",
        "Date.AddDays", "Date.AddMonths", "Date.AddYears",
        "Date.DayOfWeek", "Date.DaysInMonth",
        "DateTime.From", "DateTime.LocalNow",
        "Duration.Days", "Duration.Hours", "Duration.TotalHours"
    },
    
    ApprovedListFunctions = {
        "List.Sum", "List.Average", "List.Max", "List.Min",
        "List.Count", "List.Distinct", "List.Contains",
        "List.Select", "List.Transform", "List.First", "List.Last",
        "List.Sort", "List.Accumulate"
    },
    
    // Build whitelist environment by extracting only approved functions from #shared
    BuildWhitelistEnv = (approvedNames as list) as record =>
        let
            AllShared  = #shared,
            ValidNames = List.Select(approvedNames, each Record.HasFields(AllShared, {_})),
            Pairs      = List.Transform(ValidNames, each {_, Record.Field(AllShared, _)}),
            WhitelistRecord = Record.FromList(
                List.Transform(Pairs, each _{1}),
                List.Transform(Pairs, each _{0})
            )
        in
            WhitelistRecord,
    
    // Build the combined safe environment
    AllApproved = List.Combine({
        ApprovedMathFunctions, 
        ApprovedTextFunctions, 
        ApprovedDateFunctions,
        ApprovedListFunctions
    }),
    
    SafeEnvironment = BuildWhitelistEnv(AllApproved)
in
    SafeEnvironment

This is a reusable function you'd store in a helper query. It dynamically constructs the whitelist environment by pulling only the approved function names from #shared. The Record.HasFields check ensures graceful handling if a function name is misspelled in your approved list.

Wrapping Evaluation in Error Handlers

Malformed expressions will throw errors. Expressions that reference names not in your environment will throw errors. Divisions by zero will throw errors. Your pipeline needs to handle all of these gracefully without crashing.

The pattern here ties directly into M's error handling with try-otherwise:

let
    // A safe evaluator that returns structured result/error information
    SafeEvaluate = (code as text, env as record) as record =>
        let
            Attempt = try Expression.Evaluate(code, env),
            Result  = if Attempt[HasError] then
                [
                    Success  = false,
                    Value    = null,
                    ErrorMsg = Attempt[Error][Message],
                    ErrorKind = Attempt[Error][Reason],
                    Code     = code
                ]
            else
                [
                    Success  = true,
                    Value    = Attempt[Value],
                    ErrorMsg = null,
                    ErrorKind = null,
                    Code     = code
                ]
        in
            Result,
    
    // Test with various formulas
    TestCases = {
        "1 + 1",                          // Valid
        "Number.Round(3.14159, 2)",        // Valid (if Number.Round is in env)
        "UndefinedFunction(42)",           // Error: name not in scope
        "1 / 0",                           // Error: division by zero
        "let x = 1 in"                     // Error: incomplete syntax
    },
    
    Env = #shared,
    
    Results = List.Transform(
        TestCases,
        (code) => SafeEvaluate(code, Env)
    ),
    
    ResultTable = Table.FromRecords(
        List.Select(Results, each true),
        type table [Success = logical, Value = any, ErrorMsg = text, ErrorKind = text, Code = text]
    )
in
    ResultTable

This SafeEvaluate wrapper returns a record that always has a consistent shape, regardless of whether evaluation succeeded or failed. You can then build downstream logic that inspects Success before using Value. This is far better than letting evaluation errors bubble up and terminate your entire query.

Batch Evaluation with Error Isolation

When you need to evaluate many formulas — like applying each row of a rules config table — you want to ensure that one bad formula doesn't cause the entire batch to fail:

let
    // Rules table loaded from external config
    RulesConfig = Table.FromRecords({
        [RuleName = "HighRevenue",    Formula = "[Revenue] > 500000",      Active = true],
        [RuleName = "LowMargin",      Formula = "[Margin] < 0.15",          Active = true],
        [RuleName = "BrokenRule",     Formula = "[Revenue] / [Typo]",       Active = true],
        [RuleName = "GoodCustomer",   Formula = "[DaysLate] <= 5",          Active = true]
    }),
    
    // Source row (imagine this comes from a row-by-row application)
    SampleRow = [Revenue = 620000, Margin = 0.12, DaysLate = 3],
    
    // Build evaluation environment that includes the row's fields
    RowEnv = Record.Combine({#shared, SampleRow}),
    
    // Safe formula evaluator
    EvaluateFormula = (formula as text, env as record) as record =>
        let
            Attempt = try Expression.Evaluate(formula, env)
        in
            if Attempt[HasError] then
                [Result = null, IsError = true, ErrorDetail = Attempt[Error][Message]]
            else
                [Result = Attempt[Value], IsError = false, ErrorDetail = null],
    
    // Apply all active rules
    ActiveRules = Table.SelectRows(RulesConfig, each [Active] = true),
    
    WithResults = Table.AddColumn(
        ActiveRules,
        "Evaluation",
        each EvaluateFormula([Formula], RowEnv)
    ),
    
    // Expand the evaluation record
    Expanded = Table.ExpandRecordColumn(
        WithResults, 
        "Evaluation", 
        {"Result", "IsError", "ErrorDetail"}
    )
in
    Expanded

The isolation here is key: EvaluateFormula wraps each Expression.Evaluate call in its own try, so BrokenRule fails gracefully and you can see why, while the other rules continue to evaluate correctly.

---

Building a Runtime Formula Injection Pipeline

Let's put this all together into a production-grade pattern: a complete runtime formula injection pipeline that reads transformation rules from a configuration table and applies them to source data.

This is the kind of system you'd build for a client whose finance team maintains KPI calculation rules in a SharePoint list, and those rules need to flow through to Power BI without a developer touching the .pbix file.

let
    // =========================================================
    // CONFIGURATION (in practice, loaded from SharePoint/SQL/etc.)
    // =========================================================
    TransformationRules = Table.FromRecords({
        [
            StepOrder    = 1,
            StepName     = "FilterCurrentYear",
            StepType     = "filter",
            Expression   = "[FiscalYear] = CurrentYear",
            Active       = true
        ],
        [
            StepOrder    = 2,
            StepName     = "AddGrossMargin",
            StepType     = "addcolumn",
            ColumnName   = "GrossMargin",
            Expression   = "[Revenue] - [COGS]",
            Active       = true
        ],
        [
            StepOrder    = 3,
            StepName     = "AddMarginPct",
            StepType     = "addcolumn",
            ColumnName   = "MarginPct",
            Expression   = "Number.Round([GrossMargin] / [Revenue], 4)",
            Active       = true
        ],
        [
            StepOrder    = 4,
            StepName     = "FlagLowMargin",
            StepType     = "addcolumn",
            ColumnName   = "IsLowMargin",
            Expression   = "[MarginPct] < LowMarginThreshold",
            Active       = true
        ]
    }),
    
    // =========================================================
    // SOURCE DATA
    // =========================================================
    SourceData = Table.FromRecords({
        [Region = "North", FiscalYear = 2024, Revenue = 450000, COGS = 320000],
        [Region = "South", FiscalYear = 2024, Revenue = 380000, COGS = 290000],
        [Region = "East",  FiscalYear = 2023, Revenue = 510000, COGS = 360000],
        [Region = "West",  FiscalYear = 2024, Revenue = 310000, COGS = 275000]
    }),
    
    // =========================================================
    // RUNTIME CONSTANTS (these get injected into the eval env)
    // =========================================================
    RuntimeConstants = [
        CurrentYear        = 2024,
        LowMarginThreshold = 0.18
    ],
    
    // =========================================================
    // SAFE EVALUATION ENVIRONMENT
    // =========================================================
    BaseEnv = [
        // Math
        Number.Round       = Number.Round,
        Number.Abs         = Number.Abs,
        Number.Power       = Number.Power,
        
        // Text
        Text.Upper         = Text.Upper,
        Text.Lower         = Text.Lower,
        Text.Contains      = Text.Contains,
        
        // List aggregates (useful in filter expressions)
        List.Contains      = List.Contains
    ],
    
    // =========================================================
    // STEP APPLICATOR
    // =========================================================
    ApplyStep = (currentTable as table, stepRow as record) as table =>
        let
            StepType    = stepRow[StepType],
            ExprText    = stepRow[Expression],
            
            // Build environment: base functions + runtime constants + access to current row
            // (current row fields are accessed via `each [FieldName]` syntax — no injection needed)
            Env = Record.Combine({BaseEnv, RuntimeConstants}),
            
            // Apply based on step type
            Result = 
                if StepType = "filter" then
                    let
                        // For filter, generate a predicate function
                        PredicateCode = "(row) => let " &
                            // Inject column accessor shorthand
                            "_ = row, " &
                            "result = " & ExprText & " " &
                            "in result",
                        // Actually, simpler: use each with record field syntax directly
                        FilterCode    = "Table.SelectRows(T, each " & ExprText & ")",
                        FilterEnv     = Record.Combine({Env, [T = currentTable]}),
                        Filtered      = Expression.Evaluate(FilterCode, FilterEnv)
                    in
                        Filtered
                        
                else if StepType = "addcolumn" then
                    let
                        ColName   = stepRow[ColumnName],
                        AddCode   = "Table.AddColumn(T, """ & ColName & """, each " & ExprText & ")",
                        AddEnv    = Record.Combine({Env, [T = currentTable]}),
                        WithCol   = Expression.Evaluate(AddCode, AddEnv)
                    in
                        WithCol
                        
                else
                    error "Unknown step type: " & StepType
        in
            Result,
    
    // =========================================================
    // PIPELINE EXECUTOR
    // =========================================================
    ActiveRules = Table.Sort(
        Table.SelectRows(TransformationRules, each [Active] = true),
        {{"StepOrder", Order.Ascending}}
    ),
    
    StepList = Table.ToRecords(ActiveRules),
    
    // Accumulate: each step receives the output of the previous step
    FinalResult = List.Accumulate(
        StepList,
        SourceData,
        (state, step) => 
            let
                Attempt = try ApplyStep(state, step)
            in
                if Attempt[HasError] then
                    error "Step '" & step[StepName] & "' failed: " & Attempt[Error][Message]
                else
                    Attempt[Value]
    )
in
    FinalResult

This pipeline is genuinely production-ready in structure. Each step gets isolated error handling. The accumulator ensures correct ordering. Runtime constants (like CurrentYear and LowMarginThreshold) are injected at the environment level, meaning business users can change them without touching the formula strings. The step type dispatch is extensible — you can add "sort", "rename", "pivot" and so on.

---

Performance Implications and Optimization

Expression.Evaluate is not free. Each call involves string parsing, AST construction, and evaluation. For typical use — evaluating a few dozen configuration rules — this overhead is negligible. But there are patterns that will cause serious performance problems.

The Biggest Mistake: Row-Level Evaluation

The most common performance anti-pattern is calling Expression.Evaluate once per row:

// DON'T DO THIS for large tables
Table.AddColumn(
    MyTable,
    "ComputedValue",
    each Expression.Evaluate(
        [FormulaColumn],    // different formula per row
        Record.Combine({#shared, _})
    )
)

If your table has 100,000 rows, this calls Expression.Evaluate 100,000 times. Each call parses and compiles the formula string from scratch. For large tables this can take minutes.

The correct approach depends on your scenario:

If you have a small number of distinct formulas applied to many rows: group rows by formula, apply each formula to its entire group using a single Expression.Evaluate call, then recombine.

If formulas differ per row but have a known structure: don't use Expression.Evaluate at all. Instead, build a function factory that generates M function values once and applies them efficiently.

let
    // Generate a function VALUE (not a table result) from a formula string
    MakeRowFunction = (formula as text) as function =>
        Expression.Evaluate(
            "(row) => " & formula,
            Record.Combine({#shared, [row = []]})  // dummy to ensure 'row' binding works
        ),
    
    // This compiles once, returns a reusable function
    MarginCalculator = MakeRowFunction("[Revenue] - [Cost]"),
    
    // Apply efficiently to entire table — no repeated string parsing
    Result = Table.AddColumn(
        SourceData,
        "GrossMargin",
        each MarginCalculator(_)   // _ is the current row record
    )
in
    Result

By compiling the formula once into a function value and then applying that function to every row, you do the expensive string-parsing work exactly once and the per-row work is just a function call — which the M engine handles efficiently.

Query Folding Considerations

Expression.Evaluate completely breaks query folding. The M engine cannot translate dynamically generated M expressions into SQL or another native query language. Any step that uses Expression.Evaluate will pull data into memory before the evaluation happens.

This means your pipeline architecture matters: apply Expression.Evaluate-based transformations after you've filtered your data down to the minimum necessary set, not before. Push as much filtering as possible into the source query where folding can occur, then apply dynamic transformations on the reduced dataset. For more on this architectural consideration, review implementing custom query folding logic in M.

---

Hands-On Exercise

Build a dynamic KPI calculation engine using the following specification:

Scenario: Your organization tracks 5 KPIs for each sales region. The KPI definitions (formulas) are stored in a configuration table. Business analysts need to be able to update the formulas without developer involvement.

Step 1: Create a configuration table with these KPIs:

KPIName	Formula	Format
TotalRevenue	List.Sum(Table.Column(RegionData, "Revenue"))	currency
AvgDealSize	List.Average(Table.Column(RegionData, "DealSize"))	currency
WinRate	List.Count(Table.SelectRows(RegionData, each [Outcome] = "Won")[Outcome]) / List.Count(RegionData[Outcome])	percent
TopDeal	List.Max(Table.Column(RegionData, "Revenue"))	currency
DealCount	List.Count(RegionData[Outcome])	number

Step 2: Create a source data table with at least 10 rows covering 2 regions, with columns: Region, Revenue, DealSize, Outcome.

Step 3: Build an evaluation function that:

• Takes a region name and the KPI config table as parameters
• Filters source data to that region (producing RegionData)
• Evaluates each KPI formula with RegionData injected into the environment
• Wraps each evaluation in try-otherwise to catch errors
• Returns a table with columns: KPIName, Value, Format, IsError

Step 4: Apply your function to both regions and combine the results with a Region column added.

Challenge extension: Add a Threshold column to the KPI config and generate a BelowThreshold boolean column in your output that flags KPIs where the value fell below the threshold. The comparison should itself be evaluated dynamically using Expression.Evaluate.

---

Common Mistakes & Troubleshooting

"Expression.Evaluate didn't find [FunctionName]"

Cause: You're using a function name in your expression that isn't in the environment you passed.

Fix: Check what's actually in your environment. If you're using a minimal environment, make sure every function your formula references is included. If you're using #shared, this error shouldn't occur for standard library functions — which means the function name is misspelled.

// Diagnostic: check if a specific function is in your environment
Record.HasFields(YourEnvironment, {"Number.Round"})  // returns true/false

Nested Quote Escaping Errors

Cause: Your generated code contains string literals, and the escaping is wrong.

Fix: Use the EscapeForMString helper from earlier. Never concatenate raw user strings directly into code templates. When generating code that contains string literals, always double every double-quote character.

// Wrong: will break if columnValue contains double quotes
"Table.SelectRows(T, each [Name] = """ & columnValue & """)"

// Right: escape the value first
"Table.SelectRows(T, each [Name] = """ & Text.Replace(columnValue, """", """""") & """)"

"The expression failed because of a cycle"

Cause: Your generated code creates a circular reference — an expression that references itself directly or indirectly.

Fix: This is a code generation logic error. Review how you're building environment bindings. If you inject T = T where T is the result of a query that itself calls Expression.Evaluate with T in scope, you can create a cycle. Keep injected data simple and make sure it's fully computed before being passed as an environment binding.

Performance Collapse on Large Tables

Cause: Expression.Evaluate called inside Table.AddColumn or Table.TransformColumns, causing per-row invocation.

Fix: Compile formula strings to function values once using the MakeRowFunction pattern, then apply the function to the table. If formulas vary per row, group by unique formula, apply each formula to its group, then recombine.

Expression Silently Returns Null

Cause: The expression evaluates successfully but the formula references a field name that doesn't exist in the current row — M returns null for missing field accesses in some contexts.

Fix: Validate that all field names referenced in formulas actually exist in your source data schema. You can add a pre-flight check that extracts field references from formula strings (a simpler version of a full parser) and validates them against the schema.

Environment Record Too Large

Cause: You're building an environment that includes large tables as bindings, and this environment is being recreated inside a per-row loop.

Fix: Build the environment once outside any transformation loop, store it as a named value, and reference that named value inside your transformation. M's lazy evaluation should handle this correctly if you structure it right, but it's easy to accidentally force repeated recomputation.

---

Summary & Next Steps

You've now seen Expression.Evaluate from every angle that matters in production: how the environment parameter actually controls scope, how to build minimal or full-access environments depending on your trust model, how to generate valid M code strings with proper escaping, how to build sandboxed execution pipelines with per-step error isolation, and how to avoid the performance traps that make dynamic evaluation impractical at scale.

The key architectural principles to take away:

• Compile once, apply many times. Use Expression.Evaluate to generate function values, then apply those functions using native M table operations.
• Whitelist by default. Curate your environment explicitly unless you have a specific reason to use #shared.
• Isolate errors at the step level. Use try inside your applicator functions so one bad formula doesn't cascade.
• Inject data as bindings, not as strings. Pass complex values through the environment record rather than serializing them into code strings.
• Keep dynamic evaluation away from your source query. Apply it after folding-compatible filters have run on the source.

This capability connects naturally with several other advanced M patterns. If you want to go deeper on the parsing and validation side — actually understanding the structure of M expressions rather than treating them as opaque strings — explore building a custom M language parser and tokenizer. For understanding how the M engine decides when and whether to evaluate your dynamic expressions, the deep dive on lazy evaluation and dependency graphs will sharpen your mental model considerably. And if you're building this kind of dynamic pipeline as part of a larger ETL architecture, multi-stage ETL pipeline orchestration covers the broader structural patterns that make complex query systems maintainable.

Dynamic code evaluation is one of the features that separates M from most other data transformation languages. Used carefully, it opens up a category of self-describing, user-configurable data pipelines that would be impossible to build otherwise.