Power QueryExercises

Power Query M vs DAX: where each calculation belongs

Comparison · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Two engines, two moments

Power Query runs when data is refreshed. Its output is fixed until the next refresh. DAX runs when a report is viewed: every time a user clicks a slicer, measures recalculate for the current selection. That timing difference decides most placement questions.

JobPower Query (M)DAX
Fix types, cultures, textYesPossible, but wrong place
Unpivot, append, combine filesYesNo
Lookup columns from another tableMerge, or leave to a relationshipRELATED across a relationship
Row-level calculation (price × qty)Yes, as a typed columnCalculated column (stored), or inside a measure
Total sales for the selected regionNo (fixed at refresh)Yes, a measure
% of total for the current filterNoYes
Year-to-date, same period last yearOnly for fixed periodsYes, time intelligence
Rank that changes with the selectionNoYes

The percentage trap

A share of total computed in Power Query is correct for the whole table and wrong as soon as someone filters the report. Here the share is computed at refresh over all regions:

let
    Sales = #table({"Region", "Amount"}, {{"North", 600}, {"South", 300}, {"West", 100}}),
    Total = List.Sum(Sales[Amount]),
    Shares = Table.AddColumn(Sales, "Share of all regions", each [Amount] / Total, type number)
in
    Shares

Result

RegionAmountShare of all regions
North6000.6
South3000.3
West1000.1

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

If a user filters the report to North and South, these stored shares still say 0.6 and 0.3, which no longer add up to 100%. A DAX measure dividing by the total of the current selection would show 0.67 and 0.33. Name precomputed columns honestly ("Share of all regions") or move the calculation to DAX.

Good reasons to calculate in Power Query

  • The value never depends on report filters (a cleaned name, a VAT-inclusive price, a fiscal period).
  • It reduces model size or complexity (unpivoting, removing unused columns).
  • Several reports or tools consume the same table (Excel and Power BI).
  • The logic needs data from several files or sources combined first.

In Excel

The same split applies with Power Pivot: Power Query shapes and loads to the Data Model, DAX measures power PivotTables. Without the Data Model, loaded tables feed worksheet formulas and PivotTables, which play the role DAX plays in Power BI.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.