Power Query vs DAX: Which One Should You Use for That Calculation?

Power Query shapes the data that arrives; DAX answers questions at runtime.

Every Power BI builder hits the question within their first month: this calculation I need - does it belong in Power Query or in DAX? And because both languages can usually produce the number, the question gets answered by familiarity rather than fitness.

The result is visible in rescued models everywhere: thirty-step applied queries laboriously recreating what one measure expresses in a line, and iterator-heavy DAX grinding through row transformations the pipeline should have finished before the data arrived. Both work, both are wrong, and both get slower every month.

The fix is a mental model, not a rulebook - one clean distinction that decides ninety per cent of cases instantly.

Key Takeaways

• Power Query shapes what arrives; DAX answers what is asked - transformation at refresh time versus calculation at query time.

• If the answer depends on the user's filters, it is a measure - the single test that resolves most cases.

• Push work as far upstream as possible: the warehouse beats Power Query beats DAX for heavy transformation.

What Is the Clean Mental Model?

Power Query runs once per refresh and shapes the data that lands in the model - rows, columns, types, structure. DAX runs at query time and answers questions whose results depend on context - what the user has filtered, clicked and selected. One prepares the ingredients; the other cooks to order.

Everything follows from when each executes. Work done in Power Query is paid once, at refresh, and stored; work done in DAX is paid on every click, by every user, forever. That is why static preparation belongs upstream and dynamic response belongs in measures - putting them the other way around either bloats the refresh or taxes every interaction.

Which Jobs Clearly Belong to Power Query?

Anything that is true of the data regardless of who is looking: cleansing, type-setting, splitting and merging columns, unpivoting cross-tab sources, filtering out rows that should never load, and standardising values. If the result would be identical for every user and every filter state, it is shape, and shape is Power Query's job.

The recognisable cases: unpivoting the budget spreadsheet's month columns into rows; trimming and casing customer names; deriving a clean date column from a text field; removing cancelled transactions at the gate; merging a lookup that enriches every row the same way. Doing these in DAX as calculated columns produces the same values at a worse price - computed at refresh anyway, but stored uncompressed-friendly and cluttering the model with logic that belongs in the pipeline.

Shape upstream, calculate at runtime - and push work as far upstream as it goes.

Which Jobs Clearly Belong to DAX?

Anything whose answer changes with the user's context: aggregations, ratios, rankings, time intelligence, share-of-total - the questions a report exists to answer. If clicking a slicer should change the result, no amount of Power Query can pre-compute it, because the combinations are effectively infinite.

The recognisable cases: year-to-date and prior-period comparisons against a proper calendar table; margin per cent that recalculates for whatever region is selected; a customer's rank within the current filter; per cent of parent in a hierarchy. These are measures - one expression each, computed live, correct in every context. The tell-tale of misplacement is the column that tries to be a measure: "profit per cent" as a calculated column is right at the row grain and wrong at every subtotal, which is exactly the bug that surfaces in the board meeting.

What About the Genuinely Ambiguous Cases?

A few jobs sit on the line, and the tiebreakers are performance and grain. Flags and groupings used heavily as slicers (age bands, size tiers) compute identically in either language - prefer Power Query, because stored low-cardinality columns slice faster than computed ones. Row-grain arithmetic needed at scale (quantity times price) belongs upstream where it folds to the source. And anything requiring values from other rows at query time - running totals that respect filters - can only be a measure.

The one pattern to treat as a smell in both languages: row-by-row logic over large tables. In Power Query it is the unfoldable custom function that turns refresh into a crawl; in DAX it is the iterator over millions of rows inside a visual. Either way, the volume is the message - heavy row logic wants a real engine, which is the next principle. These misplacements are among the first findings in any performance optimisation review, because they hide in plain sight and tax everything.

Why Is "As Far Upstream as Possible" the Senior Principle?

Because every layer upstream is better at transformation than the one below it: the source database beats Power Query, and Power Query beats DAX. Work pushed to the warehouse is done once, in an engine built for it, and shared by every model that follows; work trapped in one model's queries or measures is paid privately, repeatedly.

In practice: when the same shaping logic appears in a second model, that is the signal it belongs in the source - a view, a transformation layer, a lakehouse table. Power Query then becomes thin (connect, filter, load) and DAX becomes pure (business questions only). Estates that migrate up this gradient - often as part of an Excel to Power BI migration maturing into proper data foundations - find both their refreshes and their visuals accelerate, because each layer is finally doing the job it was built for.

How Do You Retrofit a Model That Got It Wrong?

Move logic in the safe direction first: calculated columns that behave like static shape migrate to Power Query, and Power Query steps that imitate measures retire in favour of real measures. Reconcile totals after each move - the migration is mechanical, but grain mistakes hide in it.

Sequence by payoff: the calculated columns on the largest fact table first (model size drops immediately), then the unfoldable query steps throttling refresh, then the column-pretending-to-be-a-measure bugs waiting to misreport a subtotal. Most models complete the retrofit in days, and the before-and-after on file size and visual speed makes the case for keeping the discipline.

Where Do Dataflows Fit in the Split?

Dataflows are Power Query promoted to shared infrastructure: the same transformation language, executed once in the service and reused by every model that needs the result. The moment two models repeat the same cleansing logic, that logic has outgrown the individual file and belongs in a dataflow - one definition of "clean customers" instead of three slowly diverging copies.

The placement principle extends naturally: as far upstream as possible now includes a shared layer above any single model. Transformations of organisation-wide interest live in dataflows; transformations specific to one model stay in its Power Query; logic that must respond to user interaction remains DAX. Same sequence, three shelves instead of two.

Two Languages, One Sequence

Power Query and DAX are not competitors; they are stages. Shape what arrives, then answer what is asked - and push the heaviest shaping past Power Query entirely, into engines built for it. Models structured this sequence-first stay small, fast and legible; models structured by familiarity accumulate debt in whichever language their author knew best.

The next time the question arises, ask the one test: does the answer depend on what the user has filtered? Measure if yes, shape if no - and upstream if heavy. That is the whole rulebook.