1.3.1. Power Query: Shaping Data Before It Loads
💡 First Principle: Power Query records your cleaning and shaping actions as a repeatable recipe. You perform a transformation once with clicks, Power Query writes it down as an applied step in the M language, and every refresh replays the recipe against fresh source data. You aren't editing data; you're editing instructions.
The Power Query Editor shows each query's applied steps in order. Every step takes the previous step's output as its input, so order matters: filtering rows before a merge is cheaper than after; changing a data type before splitting a column can cause errors. Selecting an earlier step shows the data as it looked at that point, which makes debugging straightforward.
Query folding is the performance concept that matters most. When the source is a database or another capable system, Power Query tries to translate your steps into a single native query (such as SQL) and send it to the source, so filtering and aggregation happen at the source and only the result travels across the network. Steps that the source can't express break folding, after which the remaining steps run inside Power Query's own engine. You can check whether a step folds by right-clicking it and looking for View Native Query, or by watching the folding indicators beside each step. A practical rule: put foldable steps (filter rows, remove columns, group by, basic joins) early, and put non-foldable steps (complex custom columns, some text operations) late.
The guiding maxim for where transformations belong, often called Roche's Maxim, is: transform data as far upstream as possible, and as far downstream as necessary. If the source database can do it, do it there; if not, do it in Power Query; only fall back to DAX calculated columns when the logic genuinely needs the model (for example, it depends on a relationship).
⚠️ Exam Trap: Adding an index column, certain custom columns, or merging with a non-foldable source early in the step list can break folding for every step after it. When a stem says "refresh is slow against a SQL Server source," look for reordering steps so foldable operations come first, not for adding more DAX.
Reflection Question: Why does a filter step that removes 90% of rows deliver a much larger refresh improvement when it folds to the source than when it runs inside Power Query after the data has been downloaded?
