DAX vs Power Query: Which One Should Do the Work?
· 7 min read
The short answer to DAX vs Power Query: Power Query (written in the M language) gets data into the model. It connects to sources, cleans, reshapes and adds columns, and it runs when the data refreshes. DAX works on the data once it is in the model. Its main job is measures: calculations that react to the filters, slicers and visuals in the report. Clean and shape in Power Query. Aggregate in DAX.
// Power Query (M): a new column, computed once per row at refresh
= Table.AddColumn(Source, "Line Revenue",
each [Quantity] * [UnitPrice] * (1 - [DiscountPct]), type number)-- DAX: a measure, computed for every cell of every visual
Completed Revenue =
CALCULATE ( SUM ( Sales[Line Revenue] ), Sales[Status] = "completed" )Below is the same Brightline Supply Co. data handled both ways, with the rule of thumb for the overlap (new columns) and the mistakes that come from mixing up the two languages.
Where each one runs
Think of a Power BI file as a pipeline:
- Source: CSV files, a database, a SharePoint folder.
- Power Query: a list of steps (filter rows, change types, merge, unpivot, add columns). Runs at refresh.
- Model: the loaded tables, relationships, calculated columns and measures. DAX lives here.
- Report: visuals query the model, which evaluates DAX measures on demand.
Power Query cannot see your slicers, because it runs before the report exists. DAX cannot reach back to the source to fix a messy CSV. That split answers most "which one?" questions.
M vs DAX at a glance
| Power Query (M) | DAX | |
|---|---|---|
| Runs | At data refresh | Calculated columns at refresh, measures at query time |
| Works on | One query (table) at a time, step by step | The whole model, through relationships |
| Good for | Cleaning, types, merging, appending, unpivoting, row-level columns | Aggregations, ratios, % of total, time intelligence, ranking |
| Reacts to slicers | No | Measures do |
| Case sensitive | Yes: names, functions and text comparisons | No: "Completed" = "completed" is TRUE |
| Syntax style | let ... in, each [Column], if ... then ... else | Excel-like functions, Table[Column] |
The example: Brightline order lines
Brightline's raw order_items export arrives like this, joined to order status:
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct | status |
|---|---|---|---|---|---|---|
| 1 | 50001 | 201 | 4 | 24.00 | 0.00 | completed |
| 2 | 50002 | 101 | 10 | 5.50 | 0.10 | completed |
| 3 | 50003 | 303 | 3 | 25.00 | 0.00 | completed |
| 4 | 50004 | 103 | 2 | 59.00 | 0.05 | completed |
| 5 | 50005 | 301 | 6 | 14.00 | 0.00 | cancelled |
The goal: a Sales fact table in a star schema (related to Products, Customers and Date), and a revenue measure by category.
Step 1: shape in Power Query
In the Power Query editor, the steps for this table might be:
let
Source = Csv.Document(File.Contents("order_items.csv"), [Delimiter = ",", Encoding = 65001]),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {
{"quantity", Int64.Type}, {"unit_price", type number},
{"discount_pct", type number}, {"status", type text}}),
Renamed = Table.RenameColumns(Typed, {
{"quantity", "Quantity"}, {"unit_price", "UnitPrice"},
{"discount_pct", "DiscountPct"}, {"status", "Status"}}),
AddRevenue = Table.AddColumn(Renamed, "Line Revenue",
each [Quantity] * [UnitPrice] * (1 - [DiscountPct]), type number),
AddSize = Table.AddColumn(AddRevenue, "Order Size",
each if [Quantity] >= 6 then "Bulk" else "Standard", type text)
in
AddSizeThe loaded table gains two columns:
| order_item_id | Quantity | Status | Line Revenue | Order Size |
|---|---|---|---|---|
| 1 | 4 | completed | 96.00 | Standard |
| 2 | 10 | completed | 49.50 | Bulk |
| 3 | 3 | completed | 75.00 | Standard |
| 4 | 2 | completed | 112.10 | Standard |
| 5 | 6 | cancelled | 84.00 | Bulk |
Note what was not done here: the cancelled row was not removed. If you filter it out in Power Query, it is gone from the model, and nobody can later report on cancellations. Filter at load time only for rows that are never needed.
Step 2: measure in DAX
With Products[Category] on a table visual and the measure from the top:
| Category | Completed Revenue |
|---|---|
| Coffee & Kitchen | 96.00 |
| Paper & Print | 161.60 |
| Cleaning | 75.00 |
| Total | 332.60 |
The cancelled 84.00 of All-Purpose Cleaner is excluded by the measure, not by the data load, so a second measure for cancelled revenue is one line away. A percentage of total, a YTD or a rank could not be done in Power Query at all, because they depend on what the visual shows. See DAX CALCULATE explained and how to create a date table.
Power Query vs DAX calculated column
Both can add a column like Line Revenue or Order Size. The DAX versions:
Line Revenue = Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct])
Order Size = IF ( Sales[Quantity] >= 6, "Bulk", "Standard" )They return the same values. Prefer Power Query when the column only uses values from the same row, or from a table you can merge in:
- Columns from Power Query are compressed together with the rest of the table. DAX calculated columns are added afterwards and can compress less well.
- The logic sits with the other cleaning steps, in one place.
- With a database source, Power Query can often push the step down to the database (query folding).
Use a DAX calculated column when it needs the model: values from related tables via RELATED, aggregates through RELATEDTABLE or CALCULATE, or a column that depends on other calculated tables. A common guideline is to do work as far upstream as possible and as far downstream as necessary.
And if the value is something you will aggregate rather than slice by, it may not need to be a column at all. See measure vs calculated column.
Common mistakes
An AI assistant mixes up the two languages
Ask an AI assistant for "a custom column in Power Query that labels bulk orders" and you will sometimes get DAX syntax:
= IF([Quantity] >= 6, "Bulk", "Standard")Power Query rejects it with an error saying the name 'IF' wasn't recognized. M has no IF function: it uses lowercase if ... then ... else ..., and M is case-sensitive, so If or IF fails too. The reverse also happens: if ... then pasted into a DAX column. How to spot it: in M, columns are written [Quantity] without a table name, and functions look like Table.AddColumn or Text.Upper. In DAX, columns are written Sales[Quantity] and functions are capitalised words like CALCULATE.
Case-sensitive filters in Power Query
Table.SelectRows(Typed, each [status] = "Completed") keeps no rows when the data says "completed". M compares text exactly. DAX's Sales[Status] = "Completed" would match. When a Power Query filter returns an empty table, check capitalisation and stray spaces first (Text.Trim, Text.Lower).
Trying to build a ratio in Power Query
A margin % or share column computed in Power Query is fixed per row, and summing or averaging it in a visual gives a wrong result. Ratios belong in DAX measures with DIVIDE.
Filtering out rows the business will ask about later
Removing cancelled or refunded orders at load time makes revenue look simpler today and makes "how many orders were cancelled?" impossible tomorrow. Keep the rows and filter in the measure.
FAQ
Should I use DAX or Power Query?
Use Power Query to import, clean, reshape and add simple row-level columns. Use DAX for measures and for columns that need relationships. Most models use both.
Is Power Query faster than DAX?
They do different jobs, so compare the specific task. For a row-level column, Power Query is usually the better choice: it runs at refresh, may fold to the source, and the result compresses well. For aggregations in a report, only DAX works.
What is the difference between M and DAX?
M is the language behind Power Query: a step-by-step, case-sensitive language for getting and transforming data. DAX is a formula language for calculations on the loaded model, especially measures evaluated in filter context.
Can Power Query do what a DAX measure does?
Only for fixed results. Power Query can pre-aggregate with Group By, but the result does not respond to slicers. Anything that should change with the report's filters needs a DAX measure.
Is Power Query like SQL?
In purpose, yes: it selects, filters, joins (merges) and groups data before analysis, and with database sources it often translates steps into SQL. If you know SQL, see DAX vs SQL for the other half of the picture.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Completed orders by quarter” →