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 ...
続きを読む
一部表示
22 分
2025/11/0524 分
24 分