When should transformation logic live in Power Query rather than upstream SQL or DAX?

## Three places, and what each one actually gives you A Power BI model has three natural homes for logic, and the interview question is not "which is fastest" but "which properties do you need". **Upstream — a database view, a warehouse table, a dbt model.** Logic here is text in source control: reviewable, testable, diffable. Every consumer inherits it — this report, next quarter's report, the notebook the data scientist writes, the extract the finance team pulls. It runs in the engine designed for large-scale set processing. And it is fixed once for everyone when it is wrong. **Power Query.** Logic here is expressed in M as Applied Steps inside the report file. It is fast to write, visible while you build, and reaches sources nothing upstream can: a SharePoint folder of spreadsheets, a paginated REST API, a file a supplier emails. Its cost is governance — the code lives inside a binary artifact, is not reviewed, is not tested, and is copied rather than shared when a second report needs it. Publishing it as a dataflow converts it into a shared asset, which is the middle option teams reach for when the same shaping is needed twice. **DAX.** Logic here is evaluated at query time against the filters the user applied. That is its unique property and its only real justification: a measure must respond to the slicers on the page. Static row-level shaping done as a calculated column is the anti-pattern — a calculated column is materialised in the model and generally compresses less well than the same column imported from the source, so it costs memory to reproduce what the source could have handed you. ## The heuristics that actually decide it **Does more than one consumer need it?** If yes, upstream. A rule duplicated in three report files will diverge; the only question is when. This is the single strongest signal, and it outranks convenience every time. **Is it expensive?** Heavy joins, deduplication over large history, complex window logic — push it to the engine that scales, and let Power Query select from the result. This also protects refresh windows, because a folded select from a prepared view is dramatically cheaper than assembling the same thing step by step. **Is the source reachable only by a connector?** Files in a folder, an API, a spreadsheet nobody will ever load into the warehouse: Power Query is the only tool with hands on it, so shaping there is correct — not a compromise. **Must it change with what the user filtered?** Then it must be DAX. No amount of upstream preparation can precompute a value that depends on a selection made after the model was loaded. **Would you want a code review on it?** If the answer is yes — the revenue rule, the customer-active definition, anything a regulator or a CFO might ask about — that logic should not be sitting in a step list inside a binary file. ## Where teams actually go wrong The usual failure is drift, not any single wrong choice. A report ships with three cleanup steps in Power Query because it was quicker. A second report needs the same cleanup and copies the steps. Six months later there are eleven `.pbix` files with subtly different versions of the same rule and no way to diff them. Nobody made a bad decision; nobody made a decision at all. The opposite failure exists too: pushing every report-local convenience upstream, which turns the warehouse into a graveyard of single-consumer views nobody dares drop. "Only this model needs it" is a legitimate reason to keep something in Power Query, and it should be recorded as a decision rather than a default. ## The folding argument, which is often misread When a Power Query step folds, the source executes it as SQL. So a folded filter and a `WHERE` clause in a view are the *same compute in the same engine*. The choice between them is therefore not about performance at all — it is about where the code lives, who can see it, and who inherits the fix. Once a step stops folding, the calculus changes sharply: now Power Query is doing work locally that the source could have done, and the performance argument for moving it upstream becomes concrete as well as organisational. ## What to say when asked State the default — shared logic upstream, model-local shaping in Power Query, filter-dependent logic in DAX — then name the exceptions honestly: connector-only sources, prototypes that will be promoted once the definition stabilises, and the dataflow middle ground for logic shared by several reports but not owned by the data platform team. Add the governance point most candidates omit: the reason to move logic out of report files is rarely speed, it is that a report file is not a place where an organisation can review, test or version its business rules.