1.3. The Two Languages: Power Query M and DAX
💡 First Principle: Some work is best done once, before anyone asks a question: cleaning, reshaping, and combining data. Other work must happen at the moment a question is asked, because the answer depends on what the user has filtered. Power BI gives each kind of work its own language: Power Query M for preparation at refresh time, and DAX for calculation over the model.
Why care? Putting logic in the wrong place costs you either performance or correctness. Cleaning text with DAX calculated columns wastes memory and runs over data that should never have loaded dirty. Calculating a ratio in Power Query freezes it at refresh, so it ignores every slicer and produces nonsense when summed. The exam regularly asks "where should this logic go?" and expects you to reason from timing.
Think of a restaurant kitchen. Prep work (washing, chopping, portioning) happens before service, once, in bulk; that's Power Query. Plating to order happens when a specific table orders a specific dish; that's a DAX measure, computed for each visual's filter context.
| Aspect | Power Query (M) | DAX |
|---|---|---|
| Runs when | During data refresh | Calculated columns/tables at refresh; measures at query time |
| Works on | Rows and tables flowing from sources | Tables and columns already in the model |
| Best for | Cleaning, reshaping, merging, typing, filtering rows | Aggregations, ratios, time comparisons, dynamic logic |
| Recalculates when slicers change | No | Measures yes; calculated columns no |
| Authored in | Power Query Editor (applied steps) | Formula bar, DAX query view, TMDL |
⚠️ Common Misconception: Power Query M and DAX are interchangeable ways to add a column. They can produce columns that look identical in Table view, but M runs upstream at refresh and can push work back to the source, while DAX columns are computed after load and stored in memory. Transformations belong as far upstream as possible; filter-sensitive aggregations belong in measures.
