How Power Query (M) evaluates your queries, and why the performance gets weird

Vojtěch Šíma
Jul 12
15 min read

tl;dr Power Query / M Language gives you a nice, intuitive clicking experience. But if you want to level up your skills or take on more interesting projects, you'll probably need to write some M code. Understanding how M evaluates your queries can really help you get better and more efficient at it. In this article, I’ll show you a couple of examples where the evaluation can get a bit weird, and we’ll go over some common tricks to make things smoother and less flaky.

Disclaimer: I'll be mentioning stuff like let expressions, functions, records, and more. If you're not familiar with these yet, feel free to stick around anyway. But if you want to enhance the experience, fix the gap [here] first and then come back.

Intro

I normally don’t bother with an “Intro” header, but in this case, I wanted to highlight the structure of the article. Evaluation in Power Query can be a bit complex, so I’ll start by explaining some key components first. Only then will we dive into real examples and see how evaluation works in Power Query.

If you’re already familiar with the terms, feel free to skip ahead to the examples.

Lazy or eager evaluation

If you're new to writing M (or any code), you’ve probably never heard these terms. In short, lazy evaluation happens when code runs only when needed, while eager evaluation runs as soon as it's defined.

In M, most things are lazy. Specifically, let expressions, tables, lists, and records are lazy. So if you define a couple of tables but never use them in your final in expression, it’s like you never wrote them. Which is pretty handy.

On the flip side, function parameters are eager by default. That means if you call a function and pass in a variable, that variable gets evaluated right away, even if the function doesn’t end up needing it.

But it's not eager in the strictest sense. Only the parts of the code that actually contribute to the final in expression will be evaluated. So if you've got a function call stored in a variable that never gets used, it still won't be run. That's the lazy gang in action.

So what is M then? Technically, you'll find more results calling it "partly" or "partially" lazy. But since I'm from the generation that creates labels, let's make a new one. From now on, let's call it lazily eager.

Variables & Streaming

In Power Query M, variables are key to both clarity and performance. Once you define a variable in a let block, its expression is immutable; you can’t change what it does. This is also why you can't define a new variable with the same name as an existing one within the same scope. If you try, you’ll get a syntax error.

Important note: I’m talking about the expression, the code or logic behind the variable, not necessarily its evaluated value.

Each variable in a let block may be one of two things:

A data recipe – instructions on how to cook up a table, list or record on demand
A simple expression – a number, text or DateTime that, once evaluated, stays the same every time you use it (given its scope)

Why call it a recipe? Because M never builds entire datasets up front and hands them off. It’s lazy and works bottom-up. When you request your final result (in block), Power Query traces back through your variables to see what actually contributes. Only then does it start streaming - fetching rows (or small batches) from the source and pushing them through each step (filtering, adding columns, etc.), dropping anything you don’t need.

Therefore, variables holding tables or lists don’t store all the data in memory; they store only the instructions to fetch and transform it whenever you use them. That means referencing the same variable twice will re-run the same recipe unless you explicitly buffer it, which breaks the stream. Nonetheless, letting the stream flow without buffering is usually the most efficient approach, unless you plan to reference the same variable repeatedly. In that case, you can buffer and save the results in memory so you don’t re-fetch data every time.

On the note of re-running, Power Query does include a persistent cache for source requests that can sometimes suppress a second fetch, but it’s notoriously inconsistent. So it's just better to design your flow better to avoid expensive re-runs.

Once buffered, Power Query reads the whole dataset once, keeps it in memory, and reuses that cooked batch for every later reference.

Please note that buffering, or breaking the stream, may occur even if you don’t explicitly call Table.Buffer or List.Buffer. Operations such as sorting, grouping, and merging can implicitly introduce buffering as well.

On the other hand, scalar variables act like constants (kinda). If your variable is just a number, text or a self-contained function that doesn’t hit an external source, M evaluates it once the first time it’s needed and then reuses exactly that value for the rest of the query (within its scope).

Buffering

Buffering is the process of materializing or saving a whole dataset into memory, which stops any folding (see below) or streaming in exchange for immediate access to the dataset. This approach is great when you need to loop over a table multiple times or use it as a reference for other tables.

It also has a big downside. As mentioned, buffering stops folding and streaming, and it consumes a lot of memory. If you don’t filter or reduce the data before buffering, you might run out of memory with large datasets or significantly slow down the refresh. Keep in mind to reduce the data as much as possible before buffering.

Buffering is also shallow. If you have structured data with nested values, it won’t buffer the inner parts. It saves the outer structure and its immediate values, but not the values inside records or lists. So before buffering, think about the structure of your data and what exactly you want to include; sometimes, you may not want to buffer the nested values at all.

Example: If your table has columns containing non-expanded records or lists, the values inside those records or lists won’t be buffered.

As mentioned, buffering can be triggered explicitly by using List.Buffer or Table.Buffer, or implicitly by certain operations like grouping, sorting, or joining. Another thing that can trigger buffering is the privacy firewall. When you combine different data sources, M might isolate and materialize each source before combining them to enforce privacy.

Bear in mind that M doesn’t care whether the buffer is actually useful or not. For example, if you buffer your final step - the last variable used directly in the in block - and then load the data right after, it still gets buffered. Even though it makes no practical sense, M will still materialize the table in memory, only to discard the buffer immediately after the refresh. So in that case, you just wasted resources buffering something with zero benefit.

Query Folding

I bet you know this one. In short, M can translate certain steps into a native query that runs directly against the original data source, outside the M engine. This can dramatically improve performance.

The sources that usually support folding are relational databases and OData feeds. So in practice, many of your basic steps can be translated into SQL and executed by the database, just like writing a query in a SQL editor.

I'm mentioning query folding here because earlier I brought up streaming. These two concepts often work best together. In general, you want to fold what you can first, then stream the result, avoiding unnecessary materialization.

Just like streaming, folding can break when certain steps are used. These include pivoting, adding columns with complex logic, merging with external sources, or buffering (duh).

The exact list of what breaks folding will depend on your specific data source.

Personal take on Query Folding - SQL only

I’m a bit torn on query folding. If you’re new to the data world, you’re probably just clicking your way through Power Query without thinking too much about whether something folds or not. And honestly, that’s what we all did.

But once you get more familiar with data and the underlying sources, you might start checking if your steps actually fold. By that point, chances are you’ve already written some SQL. And if that’s the case, you might just prefer writing the logic directly in SQL instead of relying on Power Query, because then you know for sure that everything folds.

That’s how I personally approach it. I try to write as much as I can in SQL first. Later, when I need to bring in external sources or blend things in Power Query, I’ll probably break the folding anyway. So yeah, if you’re reading this, I’d genuinely like to hear how you approach folding in your own projects.

I think that's for definitions and key concepts. Let's see some M.

Lazy evaluation and variables in simple practice

A great tangible example to demonstrate lazy evaluation is using random numbers.

Nothing strange so far. Now let’s add a rounding function and a couple more variables to see how M behaves:

let
    rounder = (num as number)=> Number.Round( num, 0),
    rNum = rounder(Number.RandomBetween(1, 10) ) ,
    rNumBig= rounder(Number.RandomBetween(80, 100) ),
    createList = { rNum, rNum }
in
    createList

What do you think this will output?

Scalar variables are evaluated at most once.
- The expression assigned to rNum runs a single time when it's called in the in expression. Even though rNum appears twice in the list, both values are identical. And no, that’s not just luck, try running it 100 times, you'll always get the same repeated value.
Unused variables are ignored entirely.
- The variable rNumBig isn’t referenced in the final output (in expression), so M doesn’t bother evaluating it at all. It’s as if it was never there. That’s lazy evaluation doing its thing.

Now, let's add more expressions to showcase the behaviour.

let
    rounder = (num as number)=> Number.Round( num, 0),
    rNum = rounder(Number.RandomBetween(1, 10) ) ,
    rNumBig= rounder(Number.RandomBetween(80, 100) ),
    rNumFunction = () => rounder(Number.RandomBetween(1, 10) ),
    call_rNumFunction = rNumFunction(),
    createList = { 
        "variable rNum",
        rNum, 
        rNum, 
        "no variable direct expression",
        rounder(Number.RandomBetween(1, 10) ),
        rounder(Number.RandomBetween(1, 10) ) ,
        "call function rNumFunction",
        rNumFunction(),
        rNumFunction(),
        "variable that calls rNumFunction",
        call_rNumFunction,
        call_rNumFunction
    }
in
    createList

Output:

I added text values to make the output easier to follow. Ignoring the text rows, here’s what’s happening:

Rows 2&3 are from the variable rNum, which is evaluated once and reused. So the values are the same.

Rows 5&6 come from direct expressions that generate random numbers. Since they're not stored in variables, they are evaluated each time, so the values differ.

Rows 8&9 are from calling rNumFunction() directly. This behaves the same as the previous two; each call gives a fresh result, just wrapped inside a function.

Rows 11&12 show what happens when you call rNumFunction() once and store the result in a variable (call_rNumFunction). Now, since it’s a variable, it behaves like rNum, it holds a single value, used twice.

Finally, rNumBig is omitted again, because it’s not part of the final result in the in expression.

When Order Matters

Along with the evaluation of variables, there’s another important thing to understand: order matters. Specifically, the order of expressions inside the in block affects how and when values get evaluated. If you’re creating a list of values, their position directly impacts how and when the engine computes them.

Let’s look at a nice example that shows this clearly.

let
  start = DateTime.LocalNow(),
  end = DateTime.LocalNow(),
  createList = {start, end, DateTime.LocalNow() }
in
  createList

Let’s look at an example. We use DateTime.LocalNow() multiple times and store the result in different variables. From what we know so far, the values should be practically identical. There’s no trickery here.

In many cases, you might actually see the timestamps as completely identical. That’s because the evaluation can happen so fast that all values fall within the same tick. The engine operates somewhere between 0.5 and 15 milliseconds of precision.

Identical or nearly identical timestamps

Now let’s spice things up. We introduce a delay using Function.InvokeAfter() to simulate waiting. We wait 10 seconds before running one of the expressions.

let
  start = DateTime.LocalNow(),
  end = DateTime.LocalNow(),
  createList = {
    start,
    end,
    Function.InvokeAfter( ()=> DateTime.LocalNow(), #duration(0,0,0,10) ) 
  }
in
  createList

This time, we expect the first two values to be almost the same, and the third one to be delayed by about 10 seconds.

And that’s exactly what we get. Still nothing tricky. But now, let’s take it a bit further and look at why the order in the list matters.

let
  start = DateTime.LocalNow(),
  end = DateTime.LocalNow(),
  createList = {
    start,
    end,
    Function.InvokeAfter( ()=> end, #duration(0,0,0,10) ),
    DateTime.LocalNow(), 
    end
  }
in
  createList

Any thoughts?

In this query, variables start and end capture timestamps at essentially the same instant. They're evaluated immediately when the list needs them (rows 1 & 2 ).

The third element uses Function.InvokeAfter() with a 10-second delay. But notice carefully: the function itself doesn't call DateTime.LocalNow() again, it simply references the already evaluated variable end. Since end was previously evaluated (when we needed it for the second element), the function is just waiting 10 seconds to return that already-stored timestamp. No new timestamp is generated here; we're effectively delaying returning the same value we already captured earlier.

The fourth element is another direct call to DateTime.LocalNow(), which is updated when the list reaches this item, following a 10-second delay, which results in a fresh timestamp approximately 10 seconds after the first two. This proves that we indeed waited, even tho the third item didn't reflect that.

The fifth element is another reference to end, which gets displayed after a delay, but the value itself stays consistent with its original evaluation.

Functions & lazy and eager evaluation

Let's look at how functions are evaluated:

let
    first = error "some error",
    second = 100,
    niceFunction = (optional a)=> 60,
    call = niceFunction()
in
    call

This is what’s gonna happen:

Inside the in expression, we have the variable call, which points to the function niceFunction and calls it without parameters. niceFunction's job is to return 60, so it does, and that’s the result. No other variables are evaluated.

Since we call it without parameters, and as you remember, function parameters are eager (inside the call), we avoid triggering any eager evaluation here.

Now, let's modify it. What's gonna happen here:

let
    first = error "some error",
    second = 100,
    niceFunction = (optional a)=> 60,
    call = niceFunction(first) // added "first"
in
    call

If you think you’ll still get 60 no matter what you pass into the function, you’d mostly be right, with one big exception: errors.

Function parameters in M are evaluated eagerly, so the moment we call niceFunction(first), M tries to evaluate first. If first was just 100 or something harmless, it would be evaluated and discarded, and you'd still get 60.

But in this case, first is an error, and since it gets evaluated immediately, the entire query fails before niceFunction even runs. The actual output is the error from the variable first, not 60.

When an error is raised, M stops evaluating the rest of the expression. The error itself becomes the result.

Last example, what's gonna happen here?

let
    first = error "some error",
    second = 100,
    niceFunction = (optional a)=> 60,
    call = niceFunction(first),
    anotherCall = niceFunction(second) // this line is added
in
    anotherCall

We’re calling anotherCall, which calls niceFunction with second as the parameter. The variable second gets evaluated immediately. It’s just 100, so no issue. Then niceFunction runs and returns 60, and that becomes the final result.

Now here’s the key part: the variable call is not used in the output chain. Even though it includes a function call with a parameter (first), it’s never referenced, so M doesn’t evaluate it. The parameter in that function call would normally be evaluated eagerly, but laziness still wins here because the whole thing is unused.

Laziness in functions & Higher-Order

Perhaps you want to achieve full laziness in M code; can you do that? Unfortunately, no, but there's a workaround through a thing called "Higher-Order".

Higher-order functions let you postpone eager evaluation. Instead of passing a function’s result, you pass the function itself. When M evaluates that parameter, it only registers the function object rather than running its body.

If you create a blank query and type:

= Table.AddColumn

You will see the built-in description of the function: its parameters and some usage hints. For custom functions, you only get the parameter list and a GUI for manual invocation, unless you include a meta description in your function definition.

You can use this to avoid eager evaluation of expensive expressions. Check this example:

let
    first = ()=> error "some error",
    second = ()=> 100,
    niceFunction = (
        isTrue as logical,
        a as function,
        b as function
        ) as any =>
            if isTrue then a() else b(),
    call = niceFunction(false, first, second) 
in
    call

This is similar to the earlier examples. The only difference is that we have wrapped first and second in function definitions, and our niceFunction now expects three mandatory parameters.

Here, first and second are zero-argument functions, and niceFunction requires a logical flag plus two functions. When you initiate the call variable, M evaluates each argument just enough to recognize that it is a function. It does not run the body/expression of first or second at that point, so there is almost no compute cost. Only when niceFunction evaluates its if statement does it invoke either a() or b(), executing exactly one of the thunks.

A thunk is simply a zero-argument function whose sole purpose is to delay/postpone/defer the evaluation of an expression until you explicitly invoke it.

Notice that because we passed false as the first argument, we do not trigger the error in first. Although M evaluates the variable first (second parameter) to confirm it’s a function, it does not execute its body, so no error is thrown. Instead, niceFunction calls second, returning 100. In other words, wrapping expressions in zero-argument functions delays any error (or other expensive work) until you explicitly invoke that thunk.

This pattern lets you simulate laziness in M by wrapping expressions in functions and evaluating them only when you need their results.

Scope of variables

Variables can be nested inside each other. Managing a variable's scope is an important part of writing good M code. Let’s look at how scope can affect evaluation. This might feel like a no-brainer to some, but I want to show it clearly, just in case.

Let’s reuse our example with random numbers. Assume we have a base table, and we want to add new columns that combine an existing column with a random number.

To avoid repeating the same formula for generating the number, I’ll first create a variable that handles that logic. Then I’ll call it. This approach doesn’t change the behaviour—it acts the same as if you wrote the random function directly inline when creating the column:

rNum = ()=> Number.Round( Number.RandomBetween(0,10), 0 )

Let's add a column the traditional way.

addRandom  = Table.AddColumn(tbl, "addRandom", each let random = rNum() in [value] + random, Int64.Type)

This gives us a random number for each row.

Whether you write it like this with a let inside the function, or you skip let and just call rNum() directly:

addRandom  = Table.AddColumn(tbl, "addRandom", each [value] + rNum(), Int64.Type)

The behaviour is the same. Each row gets its own random number.

What does change the behaviour, though, is if you move the let outside the function:

addRandom2  = Table.AddColumn(addRandom, "addRandom2", let random = rNum() in each [value] + random, Int64.Type)

Changing the variables' scope changes the outcome

Now you’ll see the same result in every row. And again, it’s not luck; you’ll consistently get the same value for all three rows.

To understand why this happens, you need to know what Table.AddColumn() actually does. After defining the source table and the new column name, it expects a function that will be applied row by row. How you write that function is up to you.

But here’s the key part: you’re free to define anything before the row function. That means you can define a variable that’s outside the scope of the row. When the row function (each) runs, it uses that pre-evaluated variable. The value is already locked in; it won’t change for different rows.

Defining the variable this way is the same as if you created a physical step before addRandom2 and then referenced that step. The scope behaves exactly the same.

So remember, these are very different:

let random = rNum() in each [value] + random

each let random = rNum() in [value] + random

Evaluation of records

First of all, if you’ve made it this far, impressive, very nice. Second, you’ve probably already picked up on how records are evaluated just from the earlier examples. But I haven’t given you a clear, focused example yet, so let’s fix that.

Records are lazy.

The values inside a record won't be evaluated until you actually need them. One of the best ways to show this is by using a table that includes a column of records. This is super common when working with REST APIs that return JSON, or any kind of semi-structured data that isn’t normalized like a traditional relational database.

We’ll start with a simple table where the timestamp column uses DateTime.LocalNow().

Now let’s add a new column, and instead of a plain value, we’ll define a record with a new datetime:

addNewTimestamp = 
Table.AddColumn(
  tbl,
  "recordColumn",
  each [ timestamp2 = DateTime.LocalNow() ],
  type [ timestamp2 = datetime ]
)

If you’re not familiar with this style: you’re defining new columns as records. In this case, one field equals one column. Then in the fourth argument, you define the type using the same record structure. This helps preserve the field’s type when you expand it later.

Output:

Now here’s the key part:

What actually gets evaluated right away is the outer structure; the record column is added to the table. But the contents of each record aren’t evaluated yet. They stay unevaluated until you need them, like when you expand the record or click into it in the preview.

The preview "pane" is a perfect way to see this in action. If you expand the record column, you’ll notice that both timestamp columns (the original and the one inside the record) will be nearly identical. That’s because they both get evaluated at the same time, on demand. If you spot any differences, they’re more likely to show up between rows, not between columns, since the engine processes row by row.

Back to the preview:

Every time you click on one of the record cells to peek inside, that particular value gets evaluated right then. So if you click the same cell multiple times, you’ll see it return different values. Try spamming the cell, you’ll see the timestamp update with every click.