Picture this: you've built a Power Query solution with a dozen steps, pulling data from a slow database. You add a filter early in the query to cut the dataset down from two million rows to five thousand. Yet somehow, the query still takes just as long as before. Meanwhile, a colleague working on the same data source added what looks like an extra step to their query, and their refresh time actually improved. What's going on?
The answer lies in something Power Query is doing behind the scenes that most users never think about: evaluating your query steps in a specific order, skipping work it doesn't need to do, and sometimes reorganizing the entire execution plan to be more efficient. This isn't magic — it's a well-defined system called lazy evaluation, and it runs on top of something called a dependency graph. Once you understand these concepts, you'll stop being surprised by query behavior and start designing transformations that are genuinely faster and more predictable.
By the end of this lesson, you'll be able to reason about why Power Query evaluates steps in the order it does, when it chooses not to evaluate something at all, and how to write M code that works with the evaluation engine instead of fighting it.
What you'll learn:
let expression controls what gets evaluated and whenThis lesson is part of the Advanced M Language path, but it's designed as a foundation topic — so no advanced knowledge is assumed. You should be comfortable with:
let...in expressionIf you've ever clicked "Advanced Editor" and read through the generated code, you're ready.
Before we can talk about lazy evaluation, we need to understand evaluation itself.
When Power Query runs your query, it doesn't just execute your steps line by line from top to bottom like a Python script. Instead, it reads the entire query as a declarative description of what you want the result to be. Your job as the query author is to describe the desired output. The M engine's job is to figure out the most efficient way to produce it.
Think of it like a recipe versus a meal plan. A Python script is a recipe — follow these steps in this order. An M query is more like a meal plan: here's what I want on the table at the end, now figure out how to make it. The M engine reads everything first, understands the relationships between steps, and then decides what to actually execute.
This matters because it means the order your steps appear in your code is not necessarily the order they run — and some steps might not run at all.
All M queries are built around a let...in expression. Here's a realistic example from a sales analysis query:
let
Source = Sql.Database("sales-server", "SalesDB"),
SalesTable = Source{[Schema="dbo", Item="FactSales"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2024, 1, 1)),
RemovedColumns = Table.RemoveColumns(FilteredRows, {"InternalNotes", "LegacyID"}),
RenamedColumns = Table.RenameColumns(RemovedColumns, {{"CustID", "CustomerID"}}),
FinalResult = Table.Sort(RenamedColumns, {{"OrderDate", Order.Descending}})
in
FinalResult
Each line inside let defines a binding — a name that refers to a value or expression. The in clause tells the engine what you want returned.
Here's the crucial insight: the engine starts from the in clause and works backward. It asks: "To produce FinalResult, what do I need?" The answer is RenamedColumns. Then it asks: "To produce RenamedColumns, what do I need?" And so on, tracing back through the dependency chain until it reaches something it can actually evaluate — in this case, the connection to the SQL database.
This backward-tracing process is how the dependency graph gets built.
A dependency graph is a map of which steps depend on which other steps. It's a directed graph — each step points to the steps it needs as inputs.
For the query above, the dependency graph looks like this in plain text:
FinalResult → RenamedColumns → RemovedColumns → FilteredRows → SalesTable → Source
It's a simple chain here. But queries in the real world often fan out and merge back together. Imagine you're combining a customer dimension table with your sales fact table:
let
// Branch 1: Sales data
SalesSource = Sql.Database("sales-server", "SalesDB"),
SalesTable = SalesSource{[Schema="dbo", Item="FactSales"]}[Data],
FilteredSales = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2024, 1, 1)),
// Branch 2: Customer data
CustomerSource = Sql.Database("sales-server", "SalesDB"),
CustomerTable = CustomerSource{[Schema="dbo", Item="DimCustomer"]}[Data],
FilteredCustomers = Table.SelectRows(CustomerTable, each [Region] = "Northeast"),
// Merge
MergedData = Table.NestedJoin(FilteredSales, {"CustomerID"}, FilteredCustomers, {"CustomerID"}, "CustomerDetails", JoinKind.Inner),
ExpandedData = Table.ExpandTableColumn(MergedData, "CustomerDetails", {"CustomerName", "Segment"})
in
ExpandedData
Now the dependency graph has two branches that converge at MergedData:
ExpandedData → MergedData → FilteredSales → SalesTable → SalesSource
→ FilteredCustomers → CustomerTable → CustomerSource
The M engine can evaluate these two branches independently — and potentially in parallel. This is one reason Power Query can be faster than a sequential script for multi-source queries.
Key insight: The dependency graph is derived from which steps reference which other steps, not from the order they appear in your code. You could swap the order of the two branches in the
letexpression, and the engine would produce the same execution plan.
Now we get to the heart of the lesson. Lazy evaluation means: don't compute anything until its value is actually needed.
The M engine is lazy by default. When it builds the dependency graph, it only follows paths that lead to what the in clause requests. If you define a step in your let block that nothing else references, it will never be evaluated.
Here's a practical example. Suppose you're debugging a query and you define a diagnostic step to preview raw data:
let
Source = Excel.Workbook(File.Contents("C:\Data\SalesReport.xlsx"), null, true),
SalesSheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
// This step is for debugging — I was checking a specific row count
DebugRowCount = Table.RowCount(SalesSheet),
PromotedHeaders = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars=true]),
FilteredRows = Table.SelectRows(PromotedHeaders, each [Status] = "Closed"),
FinalOutput = Table.Sort(FilteredRows, {{"CloseDate", Order.Descending}})
in
FinalOutput
Notice that DebugRowCount is defined but never referenced by any other step, and it's not in the in clause. The dependency graph for FinalOutput never touches it. So the M engine never evaluates DebugRowCount — even though it looks like it should run as part of the query.
This is lazy evaluation in action, and it has real consequences:
Warning: Don't mistake "the step doesn't appear in my Applied Steps preview errors" for "the step ran successfully." If a step is never referenced, it won't appear to error — but it also isn't doing anything. This has burned more than a few developers who had validation logic they thought was running.
One of the biggest mental shifts when learning M is understanding that the lines inside a let block are declarations, not instructions.
In a language like SQL or Python, order matters because each line changes the program state. In M, each binding simply says "this name refers to this expression." There is no state. There is no mutation. FilteredRows doesn't change SalesTable — it creates a new value that is defined in terms of SalesTable.
This means you can technically write your let bindings in almost any order:
let
// Defined "out of order" — totally valid M
FinalOutput = Table.Sort(FilteredRows, {{"CloseDate", Order.Descending}}),
FilteredRows = Table.SelectRows(PromotedHeaders, each [Status] = "Closed"),
PromotedHeaders = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars=true]),
SalesSheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
Source = Excel.Workbook(File.Contents("C:\Data\SalesReport.xlsx"), null, true)
in
FinalOutput
This query is completely valid. The engine reads all declarations, builds the dependency graph, and then evaluates in the correct dependency order regardless of how you've written them. The reason the Power Query Editor generates code top-to-bottom (with sources first and the final result last) is purely for human readability — the engine doesn't require it.
Tip: Write your steps in a logical top-to-bottom order for your own sanity and for anyone who will maintain your code. Just know that the engine doesn't care.
Let's trace through a concrete evaluation to make this tangible.
Using our earlier sales query, here's the sequence of events when you click "Refresh":
Step 1 — Parse. The M engine reads the entire query and parses it into an internal representation. No data is loaded yet.
Step 2 — Resolve dependencies. Starting from FinalResult (the in clause), it traces: "I need RenamedColumns." Then: "I need RemovedColumns." Then: "I need FilteredRows." Then: "I need SalesTable." Then: "I need Source." The full dependency chain is now known.
Step 3 — Evaluate leaves first. Source has no dependencies inside the query — it only depends on an external resource (the SQL database). So it's evaluated first. The engine establishes the connection.
Step 4 — Fold where possible. Here's where something sophisticated happens. The engine looks at the chain of transformations — filter, remove columns, rename — and tries to express as much of that work as a single SQL query that it sends to the database. This is called query folding. Rather than pulling all two million rows and filtering in Power Query, it might send SELECT CustomerID, OrderDate FROM dbo.FactSales WHERE OrderDate >= '2024-01-01' directly to the server.
Step 5 — Evaluate what can't fold locally. Anything the database can't handle (certain M functions, custom logic) gets computed by the M engine itself using the data returned from the source.
Step 6 — Return the result. The final evaluated value matches what the in clause requested.
This sequence explains why adding a Table.SelectRows step early in your query can dramatically improve performance — even for a local file. The engine sees the filter and can skip reading rows it doesn't need. And for database sources, it turns that filter into a WHERE clause.
Lazy evaluation is usually helpful, but it can create confusing situations.
You add a step that calls a web service to log query execution. You test it in the editor — no errors. But the logging service shows no calls were made. Why? Because the logging step isn't referenced in the dependency graph. The engine never evaluated it.
In M, side effects are not guaranteed unless the step they live in is actually needed. If you need a step to run, it must be on the path to your in result.
The inverse also happens. You reference a step only in a dead branch of your query, but the engine still reports an error from it. This usually happens when the engine partially evaluates something to determine its type before deciding whether to fully evaluate it. It's rare, but if you see an error from a step you thought wasn't being used, check your dependency graph carefully.
Two steps that don't depend on each other have no guaranteed evaluation order relative to each other. If both try to write to the same external resource (a file, a database table), you might see race conditions. This is uncommon in typical Power Query work, but it's important to know if you're using M for automation tasks.
Tip: If you need to guarantee that Step A completes before Step B, make Step B depend on Step A — even artificially. You can do this by adding a parameter to Step B that references Step A's output even if you don't use it, though this is an advanced pattern and should be used carefully.
Open Power BI Desktop or Excel and create a new blank query (Home tab → New Source → Blank Query, then open Advanced Editor).
Paste this query and experiment with it:
let
// A list of monthly sales figures
SalesData = {120000, 95000, 110000, 87000, 143000, 99000},
// Convert to a table
SalesTable = Table.FromList(
List.Transform(
List.Zip({{"Jan","Feb","Mar","Apr","May","Jun"}, SalesData}),
each [Month = _{0}, Revenue = _{1}]
),
Record.FieldValues,
{"Month", "Revenue"}
),
// This step is never referenced — watch what happens
UnreferencedStep = error "This should fail if evaluated",
// Filter to months above 100k
HighRevenueMonths = Table.SelectRows(SalesTable, each [Revenue] > 100000),
// Add a rank column
RankedResult = Table.AddColumn(HighRevenueMonths, "Rank",
each List.PositionOf(
List.Sort(Table.Column(HighRevenueMonths, "Revenue"), Order.Descending),
[Revenue]
) + 1
)
in
RankedResult
What to observe:
Run the query as-is. Notice that UnreferencedStep raises an error expression — but the query completes successfully. The error is never triggered because the engine never evaluates that step.
Change the in clause to in UnreferencedStep. Now refresh. The error fires immediately.
Change it back to in RankedResult. Now add a new step after RankedResult called ReferencedButUnused = Table.RowCount(RankedResult) + 999 without referencing it in in. Confirm it never runs by temporarily changing it to ReferencedButUnused = error "Now I should fail". The query still succeeds.
Finally, try reordering the let bindings so RankedResult appears before HighRevenueMonths in the code. Confirm the query still produces the same result.
This exercise makes the abstract concept of lazy evaluation and dependency graphs tangible. You're watching the engine prove its own behavior.
Mistake: Assuming steps run in written order. The M engine evaluates based on dependency order, not written order. If you have logic that assumes Step 3 "already happened" when Step 5 runs, you might be fine — but only because Step 5 happens to depend on Step 3. Don't rely on sequence; rely on explicit dependencies.
Mistake: Placing validation or logging in unreferenced steps.
If you write a step that checks data quality and raises an error on bad data, it must be referenced by something in your in result's dependency chain. A common fix is to build the validation into a required transformation step rather than a standalone check.
Mistake: Expecting lazy evaluation to prevent all performance issues. Lazy evaluation means the engine won't compute things it doesn't need. But it doesn't mean it will compute things efficiently. A step that is evaluated but not foldable will still pull all the data locally. Laziness optimizes what runs — query folding optimizes how it runs.
Troubleshooting tip: Isolate steps in the Applied Steps pane. Click each step name in the Applied Steps pane to preview its output. The editor evaluates each step you click. If a step is slow, the slowdown exists in that step or the steps it depends on — not downstream.
Troubleshooting tip: Use = #shared in a blank query.
In a new query, type = #shared in the formula bar. This shows all available M functions and values. It's a useful way to explore the M environment without needing external data.
Let's tie together what you've learned:
Lazy evaluation means the M engine only computes values that are actually needed to produce the in result. Unreferenced steps are never evaluated — for better (performance) and for worse (silent skips of side-effect logic).
Dependency graphs are built by the engine before any data is loaded. They map which steps depend on which, and they determine evaluation order — regardless of written order in your let expression.
let bindings are declarations, not instructions. Written order is a courtesy to readers, not a directive to the engine.
The engine works backward from the in clause, tracing dependencies to find what it actually needs to evaluate, then evaluates from the leaves (data sources) forward.
These concepts are the foundation for understanding more advanced M topics. With this mental model in place, you're ready to explore:
try...otherwise and understanding how errors propagate through a dependency graphThe M language rewards understanding its evaluation model. Once you see queries as dependency graphs instead of instruction sequences, you'll write cleaner, faster, and more predictable transformations.
Learning Path: Advanced M Language