You've built a Power Query solution that works perfectly. Then a colleague opens the file on their machine and gets a strange error — but only in one query, not the others. Or maybe you've noticed that renaming a step in one query somehow broke a completely different query you weren't even touching. Or perhaps you're staring at a slow refresh and wondering: why is Power Query downloading that table three times?
All of these situations have the same root cause: a misunderstanding of how Power Query actually evaluates your queries — what runs first, what depends on what, and why the M engine makes the choices it does. Power Query isn't a scripting language where lines execute top to bottom in a fixed order. It's a declarative, lazy-evaluation engine, and once you understand that, a whole class of confusing behaviors suddenly makes complete sense.
By the end of this lesson, you'll be able to read your query steps and understand the order in which they'll actually evaluate. You'll know how to identify and manage query dependencies, avoid the most common dependency-related mistakes, and write M code that works predictably across environments. You'll also understand why Power Query sometimes seems to do "extra work" — and how to prevent it.
What you'll learn:
You should be comfortable opening Power Query Editor, creating basic transformations (filtering rows, changing data types, adding columns), and have a general sense that M is the formula language behind Power Query. You don't need to be able to write M from scratch — we'll walk through everything together.
Before we dig into Power Query specifically, let's establish what we mean by evaluation order.
In a traditional spreadsheet or SQL script, things tend to execute in the order you wrote them: first line, second line, third line. You have direct control over the sequence.
Power Query works differently. It uses a model called lazy evaluation (sometimes called demand-driven evaluation). This means the M engine doesn't compute anything until it actually needs the result. It starts from the output — what you're asking for — and works backwards to figure out what it must compute to produce that output.
Think of it like ordering food at a restaurant. The kitchen doesn't cook every item on the menu when you sit down. It only prepares what you ordered. And if one dish requires a specific sauce to be made first, the kitchen figures that out internally — you don't direct the sequence, you just say what you want.
This has a profound implication: steps that don't contribute to your final result may never run at all. This is powerful for performance, but it also means the engine may evaluate things in a different order than you'd intuitively expect.
Every query in Power Query is a series of named steps. When you use the Power Query Editor's GUI, each transformation you apply becomes a named step in the Applied Steps pane on the right. Under the hood, each step is a named variable in M code.
Open Power Query Editor, load any table, and then click View → Advanced Editor. You'll see something like this:
let
Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ChangedTypes = Table.TransformColumnTypes(
PromotedHeaders,
{{"Date", type date}, {"Revenue", type number}, {"Region", type text}}
),
FilteredRows = Table.SelectRows(
ChangedTypes,
each [Region] = "North"
)
in
FilteredRows
Let's read this carefully. Each step takes the previous step's result as its input:
Source connects to the data source. It depends on nothing inside the query.PromotedHeaders transforms Source. It depends on Source.ChangedTypes transforms PromotedHeaders. It depends on PromotedHeaders.FilteredRows filters ChangedTypes. It depends on ChangedTypes.in clause says: give me FilteredRows — this is the query's output.This creates a linear dependency chain. To produce FilteredRows, the engine needs ChangedTypes. To produce ChangedTypes, it needs PromotedHeaders. To produce PromotedHeaders, it needs Source. So it evaluates in that order, not because you told it to, but because the dependency structure demands it.
Here's the key insight: the engine doesn't evaluate steps in the order they're written. It evaluates them in the order that satisfies the dependencies of the output. In this example, those happen to be the same — but they don't have to be.
Now let's look at a slightly more complex case. Suppose you add a step that references an earlier step directly rather than the most recent one:
let
Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ChangedTypes = Table.TransformColumnTypes(
PromotedHeaders,
{{"Date", type date}, {"Revenue", type number}, {"Region", type text}}
),
FilteredRows = Table.SelectRows(
ChangedTypes,
each [Region] = "North"
),
RowCountBeforeFilter = Table.RowCount(PromotedHeaders),
FinalResult = Table.AddColumn(
FilteredRows,
"TotalBeforeFilter",
each RowCountBeforeFilter
)
in
FinalResult
Look at the dependency graph here:
FinalResult depends on both FilteredRows and RowCountBeforeFilterRowCountBeforeFilter depends on PromotedHeadersFilteredRows depends on ChangedTypes, which depends on PromotedHeadersSo PromotedHeaders is needed by two different branches. The M engine sees this and may evaluate PromotedHeaders only once (it can cache intermediate results), or it might re-evaluate it — the behavior depends on the engine's internal optimization decisions and whether the source is a foldable query (more on that later).
What's important to understand: the written order of steps in your M code is not the evaluation order. The dependency graph is the evaluation order.
Tip: You can think of the M engine as building a tree of dependencies rooted at your output and then evaluating leaves first, working toward the root. Steps that are not in this tree are never evaluated.
Here's a consequence of lazy evaluation that surprises many people. Consider this:
let
Source = Csv.Document(File.Contents("C:\sales.csv"), [Delimiter=","]),
PromotedHeaders = Table.PromoteHeaders(Source),
ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {{"Revenue", type number}}),
FilteredRows = Table.SelectRows(ChangedTypes, each [Revenue] > 1000),
UnusedStep = Table.Sort(FilteredRows, {{"Revenue", Order.Descending}})
in
FilteredRows
Notice: the in clause returns FilteredRows, not UnusedStep. Since nothing depends on UnusedStep, the M engine never evaluates it. The sort never happens. This is completely valid M code, but UnusedStep is a dead end in the dependency tree.
This is actually useful when you're debugging. You can add intermediate steps that capture a snapshot of your data at a point in the pipeline, reference those steps in your output during testing, and then switch the in clause back to your real final step when done. The debug steps simply won't evaluate when they're not referenced.
Warning: Dead steps don't cause errors — even if they reference nonexistent columns or bad syntax in some cases — because they're never evaluated. This can create a false sense of security. Clean up unused steps before you ship a solution.
So far we've looked at dependencies within a single query. But in real Power BI or Excel projects, you typically have multiple queries that reference each other. This is where understanding dependencies becomes critical for both correctness and performance.
In Power Query, you can reference one query from another simply by using its name. Suppose you have:
Query: RawSales
let
Source = Sql.Database("server01", "SalesDB"),
SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
FilteredYear = Table.SelectRows(SalesTable, each [Year] = 2024)
in
FilteredYear
Query: SalesByRegion
let
Source = RawSales,
GroupedByRegion = Table.Group(
Source,
{"Region"},
{{"TotalRevenue", each List.Sum([Revenue]), type number}}
)
in
GroupedByRegion
Query: TopRegions
let
Source = SalesByRegion,
Sorted = Table.Sort(Source, {{"TotalRevenue", Order.Descending}}),
Top5 = Table.FirstN(Sorted, 5)
in
Top5
The dependency graph now spans queries:
TopRegions → SalesByRegion → RawSales → SQL Server
When you load TopRegions to your data model, Power Query must first evaluate RawSales and then SalesByRegion. These dependencies are resolved automatically, but they have real consequences:
RawSales fails, both SalesByRegion and TopRegions fail. Error propagation flows down the dependency chain.RawSales is slow, that slowness compounds. Every query that depends on it waits for it.SalesByRegion and another query also reference RawSales directly, Power Query may evaluate RawSales twice — once for each downstream consumer — unless it's marked as a cached intermediate query.Here's where understanding evaluation order pays real performance dividends.
Query folding is when Power Query translates your M transformations back into the native query language of your data source — typically SQL. When folding works, your SQL Server (or other database) does the heavy lifting, and only the filtered, aggregated result comes across the network.
The evaluation order matters for folding because folding can only happen on a contiguous chain of foldable steps. The moment you introduce a step that the engine cannot fold (like certain custom column operations or calling external functions), folding breaks for all subsequent steps.
Here's an example of folding breaking:
let
Source = Sql.Database("server01", "SalesDB"),
SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
FilteredYear = Table.SelectRows(SalesTable, each [Year] = 2024),
// This step folds — generates WHERE Year = 2024 in SQL
AddedCategory = Table.AddColumn(
FilteredYear,
"Category",
each if [Revenue] > 10000 then "High" else "Low"
),
// This might fold, depending on the connector
RoundedRevenue = Table.TransformColumns(
AddedCategory,
{"Revenue", Number.Round}
),
// Number.Round may or may not fold
FilteredHigh = Table.SelectRows(RoundedRevenue, each [Category] = "High")
// If folding broke earlier, this filter runs in memory, not in SQL
in
FilteredHigh
If folding breaks at RoundedRevenue, then the filter on [Category] = "High" runs in memory after downloading the entire FilteredYear result set across the network. Understanding the dependency chain lets you structure steps to maximize folding — put non-foldable steps as late in the chain as possible, after you've already filtered down to a small dataset.
Tip: Right-click any step in the Applied Steps pane and look for "View Native Query." If this option is greyed out, folding has broken at or before that step.
One trap that comes up in more complex multi-query setups is a circular dependency — where Query A depends on Query B, which depends on Query A.
QueryA → QueryB → QueryA → ... (infinite loop)
Power Query will detect this and throw an error: "A cyclic reference was detected." The engine cannot resolve the dependency tree because there's no starting point to evaluate from.
This usually happens when:
The fix is always to trace the dependency chain manually. In Power BI Desktop, go to View → Query Dependencies. This opens a visual diagram showing every query and what it depends on. If you see an arrow pointing in a circle, you've found your problem.
Parameters in Power Query are a special type of query — they resolve to a single scalar value (a text string, a number, a date). When you reference a parameter in another query, you're creating a dependency on that parameter query.
// Parameter query: FilterYear (returns the number 2024)
// In RawSales:
let
Source = Sql.Database("server01", "SalesDB"),
SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
FilteredYear = Table.SelectRows(SalesTable, each [Year] = FilterYear)
in
FilteredYear
RawSales now depends on FilterYear. If FilterYear is a manually set parameter, this is fine. If FilterYear is driven by another query (for example, reading the current year from a cell in an Excel table), then that chain extends:
RawSales → FilterYear → ExcelCellQuery → Excel Workbook
The engine evaluates the full chain. This is the correct behavior, but it means parameter changes trigger a full re-evaluation of every query that depends on them.
Let's practice tracing dependency chains. For this exercise, work in Excel or Power BI Desktop with any dataset you have available — even a simple CSV with a few hundred rows is fine.
Step 1: Create a base query
Load a CSV or Excel file as a new query. Name it RawData. Apply at least three transformation steps: promote headers, change column types, and filter out any blank rows.
Step 2: Create two derived queries
Create a second query named Summary that references RawData and groups rows to produce a count or sum by some category column.
Create a third query named TopItems that references Summary, sorts it descending, and keeps the top 10 rows.
Step 3: Trace the dependencies
In Power BI Desktop, open View → Query Dependencies and examine the diagram. In Excel Power Query, you'll need to trace this manually — look at the Source step in each query and note which query it references.
Step 4: Add a dead step and verify it doesn't run
In your RawData query, add a step after your final step that references a column that doesn't exist in your data — something like Table.SelectRows(FilteredBlanks, each [NonExistentColumn] = "test"). Give it a name like DeadStep. Make sure the in clause still returns your real final step, not DeadStep.
Refresh the query. Observe that no error occurs — because DeadStep is never evaluated.
Step 5: Move the in clause to the dead step
Now change the in clause to return DeadStep. Refresh. You'll get an error because now the engine must evaluate DeadStep to satisfy the output requirement.
Change the in clause back to your real final step.
What you've demonstrated: The M engine only evaluates what the dependency chain of the output demands. Written order is irrelevant. The in clause is the true starting point of evaluation.
Mistake 1: Assuming steps run top to bottom
Many people coming from Excel formulas or Python scripts expect sequential execution. They write a query, add a "cleanup" step at the bottom, and assume it always runs. It only runs if the output depends on it. Always check your in clause.
Mistake 2: Duplicating a query when you should reference it
If you need the same transformed dataset in two places, don't copy-paste your M code into two separate queries. Create one base query and reference it from both downstream queries. Copy-pasted code creates two independent evaluation chains, doubling the work at the source.
Mistake 3: Ignoring the folding impact of step order
Placing a non-foldable transformation early in your chain can force the entire downstream portion of your query to run in memory. Always apply heavy filtering and aggregation before non-foldable steps when working with large remote datasets.
Mistake 4: Similar query names causing wrong references
If you have queries named Sales, Sales_2023, and Sales_Final, it's easy to accidentally reference the wrong one. Power Query won't warn you — it'll just evaluate whichever query you named. Use the Query Dependencies view to verify your references are correct.
Mistake 5: Not understanding why errors propagate
When a downstream query fails, beginners often start debugging it first. But if the error message says something like "the column 'Revenue' wasn't found," the real problem might be one step upstream where a column was renamed or removed. Always trace errors back up the dependency chain to the earliest failing step.
Troubleshooting workflow: When a query fails, open the query that failed, look at its
Sourcestep, and evaluate: does the input query (or data source) produce what this query expects? Work upstream until you find where the actual problem originated.
Let's consolidate what you've learned.
Power Query uses lazy, demand-driven evaluation. The M engine starts from the output you're requesting and works backward through the dependency graph to determine what it must compute. Steps that aren't in that dependency path are never evaluated.
Within a single query, each step creates a named variable that can reference earlier steps. This forms a dependency chain, and the engine evaluates steps in the order demanded by those dependencies — not necessarily the order they're written.
Across multiple queries, one query can reference another by name, creating cross-query dependencies. These dependencies chain together, meaning errors and performance issues propagate downstream. The Query Dependencies view in Power BI Desktop is your tool for visualizing this.
Query folding — the ability to push transformations back to your data source as native SQL — is affected by the order of your steps. Breaking the foldable chain early forces subsequent steps to run in memory against a potentially large dataset.
Circular dependencies cause evaluation to fail entirely and must be resolved by restructuring your query relationships.
Where to go next:
Value.NativeQuery function for manually controlling SQL sent to a sourcetry...otherwise interacts with the evaluation orderUnderstanding how the engine thinks is the foundation for writing Power Query solutions that are fast, reliable, and easy to debug. Everything else — performance optimization, error handling, complex transformations — builds on this mental model. You now have it.
Learning Path: Power Query Essentials