Opening: The Lie Your Power BI Query Tells You
You 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 Execution
Here’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 Optimizer
Query 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 data source. If not, your query becomes a therapy case for the Performance Analyzer.
So treat the folding chain with reverence. Every time you insert an Apply Step, imagine a tiny network cable stretching from your machine to the database. Keep that cable intact, and the server does the lifting. Break it, and you’re hauling the data through that cable yourself.
This is the hidden optimizer—the ultimate test of whether your query runs elegantly or torturously. Folding determines not just speed but where the work happens. In the next section, we’ll see what truly occurs when that delegation fails, when Power BI stops teleworking and starts hauling boxes by hand. That moment, dear analyst, is the collapse of folding.
Section 3: The Collapse – When Folding Fails
When query folding collapses, the polite delegation ends and Power BI rolls up its sleeves. The engine stops outsourcing and starts doing everything itself. That’s when refresh times balloon and your machine begins auditioning for a wind‑tunnel simulation. Essentially, instead of the database applying filters and joins, Power Query drags every record across the network and performs those tasks locally. It’s the computational equivalent of driving to the warehouse, picking up each pallet by hand, and delivering it room by room. Efficient? Not remotely.
Let’s translate the metaphor. When folding works, the “heavy lifting” occurs at the data source. When folding fails, the data is materialized—fetched in bulk into the client’s memory and processed line by line. Each M transformation after the break becomes an in‑memory operation, executed by the local engine. On modest datasets, you might not notice the shift. On enterprise data, it’s apocalyptic. Memory fills, throttling begins, and suddenly Power BI looks like it’s time‑traveling back to 1998 refresh speeds.
The trigger for collapse usually feels harmless. Maybe you inserted an Index column to create unique IDs. Maybe you wrote a custom function that checks if a string starts with a vowel, because cleverness shouldn’t go unpunished. Or maybe you used the wrong join kind on a merge. Any step the provider can’t translate kills delegation instantly. Folding is binary: the moment a command cannot be pushed to the source, every downstream step executes locally. There’s no partial diplomacy once the treaty breaks.
You can spot the carnage by opening View Native Query. If it’s grayed out, folding’s corpse lies there. At that point, even filters above the breaking step may still execute locally because they’re logically downstream of unreleasable operations. The symptom is universal: identical logic, dramatically worse performance. The fix, annoyingly, starts with awareness—knowing which step broke the chain.
Now, the technical culprit is dependency ordering. Every transformation depends on previous evaluation context. When a step introduces a non‑foldable construct, the engine can’t merge it into the SQL query tree, so it creates a local evaluation branch. The Execution Engine dutifully requests all necessary data from the source, loads it into memory, and continues evaluating there. In other words, a single exotic function converts your cloud database into a glorified CSV exporter.
Picture Power BI carrying groceries again. With folding, it teleports your shopping list to the store, and your pantry fills itself. Very civilized. Without it, Power BI hops into a tiny hatchback, drives to Costco, loads every item from every aisle, drives home, and unpacks one can at a time. That’s what an “unfolded” refresh looks like. The spreadsheet crowd calls it progress bar purgatory.
Certain M functions are chronic offenders. Text analysis, row index creation, custom invokes, and transformations using external scripts (R, Python) shut the folding door. Even seemingly simple transformations like Add Column from Examples can explode into a non‑SQL pattern. Each introduces expressions the data source can’t comprehend, so Power Query must run them itself. On a ten‑row sample, everything appears instant. Turn on Scheduled Refresh against 50 million rows, and you’ve built a self‑inflicted denial‑of‑service test.
Once data moves in‑memory, Power Query fragments its execution into evaluation nodes. Each node is computed sequentially, often re‑materializing intermediate tables. The result? CPU churn, disk swaps, and unpredictable runtime. You’ll even see refresh stages that appear to “pause” midway—not because Power BI is lazy, but because Windows is paging memory to disk just to survive. The person watching this chaos might think their dataset is corrupt. It’s not. It’s merely unfolded.
Diagnostics tools reveal the proof. Query Diagnostics logs will show source reads skyrocketing. The Performance Analyzer pane inside Power BI Desktop will expose long “Evaluating” durations unrelated to visualization rendering. Hook SQL Profiler to your database: you’ll see short, quick queries early, then—nothing. Folding failed, so the rest of the workload never reaches the server. The silence is damning.
Recovering from this collapse requires humility. Stop assuming every function you can write is one the source can understand. Refactor custom logic; push it back into SQL views or Fabric transformations. Use staging queries to partition expensive operations so only manageable subsets process locally. And above all, test progressively. Each new step should fold cleanly before you trust it with production volume.
Because here’s the unspoken truth: Power BI’s performance disasters aren’t mysterious glitches—they’re human arrogance written in M. Folding fails when we forget that not every brilliant idea deserves delegation. The next section will show how Power Query itself divides execution into stages—source, transformation, and load—and how misunderstanding that hierarchy multiplies pain even after folding is fixed.
Section 4: The Three Hidden Stages of Query Execution
Once you survive folding’s collapse, it’s time to confront Power Query’s deeper structure—the part users almost never think about. Power Query doesn’t execute transformations as one continuous flow. It moves through three invisible but very real phases: Source, Transformation, and Load. Miss the distinction, and you’ll spend half your life wondering why your refresh eats hours instead of minutes.
The Source stage is the handshake. It’s where Power Query connects to your data provider—SQL Server, SharePoint, Fabric Lakehouse, whatever—and establishes what data is even on the menu. That connection isn’t your entire dataset yet; it’s a promise, a deferred list of columns and tables that could be retrieved when demanded. This stage builds metadata and schema awareness but performs almost no real computation. Think of it as a waiter writing down your order without walking to the kitchen yet.
Next comes Transformation—the stage most analysts believe is the only stage. This is where all those Steps in your pane live: filters, merges, custom columns. It’s the logic design—the part where you describe how that waiter should relay your order to the chef: “filter the salad, group the entrée, rename dessert.” What you see visually as a series of linearly executed steps is, under the hood, a dependency graph feeding an optimizer. Depending on folding, those transformations may still live at the source, or they may already be local prep work. But here’s the kicker: this stage can execute multiple times without you realizing it.
That’s because Power Query caches results opportunistically. Every time you preview data, Power Query materializes a snapshot. Each small edit can trigger a partial re‑execution of the transformation plan—even for unchanged steps. Caching hides the real cost until refresh time, when everything re‑runs in full sequence. You think you’re seeing live results, but you’re really viewing episodic replays. The source was touched three steps ago, the transform restaged once, and the Load hasn’t even started.
Speaking of which, the Load phase is where the work finally materializes: data written into the model, Dataflow storage, or Fabric destination. Any folding that survived earlier now ends. The dataset is written to the Vertipaq engine or saved out in parquet layers. Power BI doesn’t differentiate whether transformations were elegant or horrific; by Load time, it just commits the outcome. It’s the moment the waiter stops rewriting the order and actually delivers food to the table—spoiler: you can’t rearrange it anymore.
Now, here’s where these stages betray you. Each one can execute independently depending on how you build queries. If you reference a query multiple times—say, for staging and final load—Power Query may re‑evaluate the same transformation logic for each reference unless caching or staging explicitly consolidates it. It’s the ETL equivalent of making three identical copies of the same meal because you requested sauce separately.
Architects often sabotage themselves here by misunderstanding reusability. They build one “Base Query” pulling from a source, then create ten dependent queries referencing that base. Visually, it feels efficient—reuse! In practice, each dependent path may trigger its own Source and Transformation chain. If folding is lost along the way, you’ve just multiplied your network calls and processing costs by ten. The optimizer can’t guess your intent; it dutifully re‑runs the dependency trees.
Clever designers manage this hierarchy consciously. They isolate Source queries—nothing but the raw connection—then create Staging transformations that clean and reshape data once, and finally Load queries whose sole job is to bring those staged results into the model. When built correctly, each stage executes exactly once, passing minimal data between them. Built poorly, they overlap like three runners in a relay race who refuse to pass the baton cleanly—each repeating the same lap because none trusts the handoff.
Caching disguises the inefficiency. Preview windows feel instant; only full refresh reveals the truth: redundant queries hammering the same database repeatedly. Diagnostics will show separate evaluations even when transformations appear shared. The symptom is duplicated transfers and inconsistent runtime spans that make no sense until you map them back to the staging hierarchy.
The solution isn’t glamorous—it’s architectural discipline. Design with those three phases in mind. Keep Sources pure, Transforms singular, Loads minimal. Anything else is extra bureaucracy your machine has to file. Understanding these stages transforms refresh chaos into predictable performance. And now that you know the structure, the next step is obvious: learning how to fix the order itself so the engine finally behaves like it was designed to—not like a rebellious intern with a server connection.
Section 5: How to Fix the Broken Order
Now that you know why Power BI disobeys you, let’s fix it. The goal isn’t to fight the engine’s order—it’s to design within it. Think like an engineer negotiating with bureaucracy: you can’t change the system, but you can make the system behave predictably.
Start by eliminating folding breaks. Every time you insert a step, ask yourself: Can the source system understand this? If you’re connecting to SQL Server, keep transformations simple enough that they can translate to SQL syntax. Filters, joins, column renames—yes. Row-by-row string parsing—no. Every unsupported instruction breaks delegation and drags computation home. Sometimes that’s unavoidable, but it should never be accidental. Keep “View Native Query” open like a compass—if it grays out, turn back.
Then, structure your queries modularly. Staging isn’t a fancy word; it’s a boundary. Use separate queries for separate purposes: one for Source, one for Transformation, one for Load. Reference results intelligently, not mindlessly. For shared logic, build intermediary queries that perform common cleanups once instead of asking ten downstream queries to repeat them. You wouldn’t copy-paste the same formula across ten cells—you’d define it once and reference it. Power Query works the same way.
Filtering is another delicate art. “Filter early” used to be gospel, but the truth is: only filter early if folding remains intact. An early filter that breaks folding is worse than no filter at all because it forces local evaluation of everything after. Check folding first, then decide placement. In SQL-based sources, early filtered steps almost always fold well; in file-based sources, you might gain nothing.
As for custom columns—restrain your creativity. M is powerful but not omniscient. Every clever line of code you write is an opportunity for inefficiency. Heavy logic belongs where computation thrives: in SQL views, Fabric transformations, or post-load DAX. Power Query is an orchestration layer; treat it as a traffic controller, not a workshop.
Next, test folding integrity like you test backups: frequently and skeptically. Create a diagnostic query mirroring your transformation chain and verify which steps still fold. Native Query Preview and Query Diagnostics will tell you exactly where the translation stops. Think of this as unit testing for your data pipeline—because that’s precisely what it is.
Also, avoid uncontrolled reuse of queries. Each reference can trigger reevaluation. If two dependent queries share 95% of steps, consolidate them. Cache intelligently—disable background previews when experimenting, so you don’t generate invisible refreshes that cloud your performance perception. Treat your Power Query workspace as production code, not a sandbox.
Finally, adopt the mindset shift: you’re not writing for yourself; you’re writing for the engine. M language describes intent, not order. So phrase your transformations in the clearest, most translatable way possible. Avoid nesting, prefer standard functions, and simplify expressions so the optimizer can “see through” your logic. When Power Query understands you, it rewards you with folding; when it doesn’t, it punishes you with heat and fan noise.
If you take nothing else from this, take the discipline of respecting the hierarchy you can’t see. When you design queries that the engine and the source both understand, performance stops being mystical and starts being mechanical. The query “order” isn’t broken—it’s just serving a different master. Your job is to speak that master’s dialect fluently enough that your intentions survive translation. Because once they do, Power BI stops rebelling and starts cooperating.
So—build modularly, fold often, test early, and let SQL do the heavy lifting. Obedience isn’t weakness; in data engineering, it’s efficiency disguised as wisdom.
Conclusion: The Real Order of Power BI
Power BI isn’t acting out—it’s simply following invisible rules you never learned. Its quiet hierarchy of execution—source before transform before load, folding before evaluation—creates logic you can either fight or master. Understanding it eliminates “random” slowness and makes refresh time a predictable outcome, not a gamble. Learn the hierarchy, and your datasets behave with machine-like precision.
If this finally made sense of your refresh misery, then calibrate your curiosity. Subscribe and keep it running. Your data deserves obedience—and here, we train it, one query at a time.
