Why Your Power BI Query is BROKEN: The Hidden Order of Operations

Opening: The Lie Your Power BI Query Tells YouYou think Power BI runs your query exactly as you wrote it. It doesn’t. It quietly reorders your steps like a bureaucrat with a clipboard—efficient, humorless, and entirely convinced it knows better than you. You ask it to filter first, then merge, then expand a column. Power BI nods politely, jots that down, and proceeds to do those steps in whatever internal order it feels like. The result? Your filters get ignored, refresh times stretch into geological eras, and you start doubting every dashboard you’ve ever published.The truth hiding underneath your Apply Steps pane is that Power Query doesn’t actually execute those steps in the visual order you see. It’s a logical description, not a procedural recipe. Behind the scenes, there’s a hidden execution engine shuffling, deferring, and optimizing your operations. By the end of this, you’ll finally see why your query breaks—and how to make it obey you.Section 1: The Illusion of Control – Logical vs. Physical ExecutionHere’s the first myth to kill: the idea that Power Query executes your steps top to bottom like a loyal script reader. It doesn’t. Those “Applied Steps” you see on the right are nothing but a neatly labeled illusion. They represent the logical order—your narrative. But the physical execution order—what the engine actually does—is something else entirely. Think of it as filing taxes: you write things in sequence, but behind the curtain, an auditor reshuffles them according to whatever rules increase efficiency and reduce pain—for them, not for you.Power Query is that auditor. It builds a dependency tree, not a checklist. Each step isn’t executed immediately; it’s defined. The engine looks at your query, figures out which steps rely on others, and schedules real execution later—often reordering those operations. When you hit Close & Apply, that’s when the theater starts. The M engine runs its optimized plan, sometimes skipping entire layers if it can fold logic back into the source system.The visual order is comforting, like a child’s bedtime story—predictable and clean. But the real story is messier. A step you wrote early may execute last; another may never execute at all if no downstream transformation references it. Essentially, you’re writing declarative code that describes what you want, not how it’s performed. Sound familiar? Yes, it’s the same principle that underlies SQL.In SQL, you write SELECT, then FROM, then WHERE, then maybe a GROUP BY and ORDER BY. But internally, the database flips it. The real order starts with FROM (gather data), then WHERE (filter), then GROUP BY (aggregate), then HAVING, finally SELECT, and only then ORDER BY. Power Query operates under a similar sleight of hand—it reads your instructions, nods, then rearranges them for optimal performance, or occasionally, catastrophic inefficiency.Picture Power Query as a government department that “optimizes” paperwork by shuffling it between desks. You submit your forms labeled A through F; the department decides F actually needs to be processed first, C can be combined with D, and B—well, B is being “held for review.” Every applied step is that form, and M—the language behind Power Query—is the policy manual telling the clerk exactly how to ignore your preferred order in pursuit of internal efficiency.Dependencies, not decoration, determine that order. If your custom column depends on a transformed column created two steps above, sure, those two will stay linked. But steps without direct dependencies can slide around. That’s why inserting an innocent filter early doesn’t always “filter early.” The optimizer might push it later—particularly if it detects that folding back to the source would be more efficient. In extreme cases, your early filter does nothing until the very end, after a million extra rows have already been fetched.So when someone complains their filters “don’t work,” they’re not wrong—they just don’t understand when they work. M code only defines transformations. Actual execution happens when the engine requests data—often once, late, and in bulk. Everything before that? A list of intentions, not actions.Understanding this logical-versus-physical divide is the first real step toward fixing “broken” Power BI queries. If the Apply Steps pane is the script, the engine is the director—rewriting scenes, reordering shots, and often cutting entire subplots you thought were essential. The result may still load, but it won’t perform well unless you understand the director’s vision. And that vision, my friend, is query folding.Section 2: Query Folding – The Hidden OptimizerQuery folding is where Power Query reveals its true personality—an obsessive efficiency addict that prefers delegation to labor. In simple terms, folding means pushing your transformations back down to the source system—SQL Server, a Fabric Lakehouse, an Excel file, wherever the data lives—so that all the heavy computation happens there. The Power Query engine acts more like a project manager than a worker: it drafts the list of tasks, then hands them to someone else to execute, ideally a faster someone.Think of folding as teleportation. Rather than Power BI downloading a million rows, filtering them locally, then calculating averages like a sweaty intern with a calculator, it simply sends instructions to the database: “Do this for me and only return what’s needed.” The result appears the same, but the journey is radically different. One path sends microscopic data requests that feel instantaneous; the other drags entire datasets through the network because the engine decided your latest custom column “isn’t compatible.”Most users first encounter query folding by accident. They open a native SQL view, add a filter, and everything is smooth—refreshes in seconds. Then they add one more transform, say a conditional column or an uppercase conversion, and suddenly the refresh time triples. It’s not superstition. That one unsupported step snapped the delicate chain of delegation. Folding broke, and with it, your performance.In folding-friendly mode, Power Query behaves like an air traffic controller—it issues concise commands, and the data source handles the flights. When folding breaks, Power Query becomes a delivery driver who insists on personally flying overseas to collect each parcel before delivering it back by hand. You can guess which one burns more time and fuel.Now, when exactly does folding work? Primarily with simple, relational operations that the source system natively understands: filters, merges (that resemble SQL joins), renames, column removals, and basic calculations. These are cheap for the engine to describe and easy for a source like SQL Server to execute. As long as the M code compiles into a recognizable SQL equivalent, folding proceeds.The moment you introduce nonlinear or complex operations—custom functions, text manipulations, or bizarre index logic—the engine decides, “Nope, can’t delegate that,” and pulls the data back to handle it locally. It’s like a translator who gives up halfway through a speech because the other side doesn’t support sarcasm. The result: partial folding, where only the first few steps get delegated, and the rest are processed in memory on your machine.You can actually see this hierarchy in action. Right-click any step and choose “View Native Query.” If that option is grayed out, congratulations, folding just died at that point. Diagnostics will show earlier steps executed at the source but later ones marked as engine-only. Every broken link in that chain multiplies the time and data volume needed.The consequence of folding breaks isn’t subtle—it’s catastrophic. Instead of letting SQL Server apply a filter that returns five thousand rows, Power BI now pulls fifty million and filters them locally. The refresh that once ran in twenty seconds now takes ten minutes. Your CPU fans spin like jet turbines, and you start questioning Microsoft’s life choices. But the blame belongs to the M function that triggered execution on the client.Most real-world “why is my query slow” complaints are just folding issues disguised as mystery bugs. Users assume Power BI is inherently sluggish. In reality, they’ve forced it to perform database-scale transformations in a lightweight ETL layer. It’s like forcing Excel to play the role of a data warehouse—it’ll try, but it resents you deeply the whole time.Let’s trace a classic failure case. You build a table connection to SQL Server. You remove a few columns, apply a filter on Date > 202, and everything folds beautifully. Then, feeling creative, you add a custom column that uses Text.Contains to flag names with “Inc.” Suddenly, folding collapses. That one string function isn’t supported by the SQL provider, so Power Query retrieves all rows locally, executes the function row by row, and only then filters. You’ve effectively asked your laptop to simulate a server farm—using caffeine and willpower.This is why query folding is less about coding style and more about translation compatibility. Power Query speaks M; your data source speaks SQL or another language. The folding process is the interpreter turning those M expressions into native commands. As long as both sides understand the vocabulary, folding continues. The moment you introduce an idiom—like a custom function—the interpreter shrugs and switches to manual translation mode.Performance tuning, in this context, becomes less about computation and more about diplomacy. You’re negotiating with the data source: “How much of this work can you handle?” The smartest Power BI developers design queries that are easy for the source to understand. They filter early, avoid exotic transformations, and check folding integrity regularly.You can even think of folding fidelity as a status indicator for your professional competence: if your transformations still fold at the end of construction, you’ve engineered harmony between Power BI and its

Become a supporter of this podcast: https://www.spreaker.com/podcast/m365-show-podcast--6704921/support.

Follow us on:
LInkedIn
Substack