Power Query vs DAX: shape the data first, calculate afterward
DAX vs Power Query is less a contest than a division of labor inside every Power BI file. Power Query prepares data: it connects to sources, cleans columns and reshapes tables before anything loads. DAX calculates: it turns the loaded tables into totals, ratios and comparisons. New authors often reach for the wrong one, so a clear rule for each kind of task saves a great deal of rework.
- Prepare, then calculate
- Task-by-task rule
- Plain examples
Last updated
Power Query vs DAX: what does each one do?
Power Query works before the model exists. An author connects to a file or database, then removes columns, fixes data types, merges tables and filters rows. Each action becomes a step, and behind the steps sits a language named M. When refresh runs, the steps replay in order.
DAX works after the data has loaded. It is a formula language for measures, calculated columns and calculated tables. A measure such as total sales responds to the filters on a report page, so it recalculates with every click.
So one tool decides what the tables look like, and the other decides what the numbers mean. Because they act at different stages, a well-built report uses both.
How do DAX and Power Query differ at each stage?
The rows show where each one sits in the life of a report.
| Point | Power Query | DAX |
|---|---|---|
| Main job | Connect, clean and reshape data | Calculate over the loaded model |
| Language | M, mostly through a visual editor | DAX formulas |
| When it runs | At refresh, before load | At query time for measures |
| Works on | Source rows and columns | Model tables and relationships |
| Reacts to slicers | No | Yes, in measures |
| Typical output | Tidy tables ready to load | Measures and calculated fields |
| Also found in | Excel and dataflows | Analysis Services and Excel data models |
Which jobs belong in the query editor?
Anything that changes the shape of a table belongs there. Unpivoting a wide spreadsheet, splitting a code into parts and merging two lists are all preparation. Since they do not depend on report filters, they should happen once at refresh.
Fixed row-level columns belong there too. A full name joined from two fields, or a category taken from a lookup, loads like source data and compresses well. Microsoft's guidance favors this route over a DAX calculated column where it is practical.
Filtering early is a further job. When a report needs only recent years, removing old rows in the query keeps the model small. Also, many steps can fold back to the source database, so the database does the heavy work instead of your computer.
Which jobs belong in a DAX measure?
Anything that must respond to the reader belongs in a measure. Totals, averages, ratios and distinct counts all change with the selected period or region. Only a measure recalculates for each of those selections.
Time comparisons belong there as well. Year-to-date, prior-year and rolling figures rely on a date table and on filter context. DAX includes functions for exactly these, while the query editor has no view of what a reader selects.
Then there are rules that span tables. A measure can follow relationships from a sales table to products and customers, so one formula serves every visual. However, heavy row-by-row logic in DAX can slow a report, which is a sign the work should move upstream. For hands-on help with the preparation side, see Power Query consulting.
Power Query vs DAX: a four-step way to place each task
Ask these questions in order whenever you are unsure where a piece of logic should live.
Could the source do it?
First check whether a database view can supply the column. Work done at the source serves every tool, not only this report.
Does it reshape or clean?
Then, when the task changes rows, columns or types, place it in the query editor so it runs once at refresh.
Must it follow filters?
Next, when the result should change as readers slice the page, write a measure, because nothing else responds.
Document the choice
Finally, note where each rule lives, so the next author does not rebuild the same logic in a second place.
Power Query vs DAX questions from Excel users
Is the M language harder than DAX?
Most people rarely write M by hand, because the editor generates it from clicks. So the daily experience is gentle. DAX looks like Excel formulas at first, yet filter context takes time to understand. Therefore many authors find preparation easy to start and measures harder to master.
Can Power Query and DAX do the same calculation?
Sometimes. Both can add a column that multiplies quantity by unit cost, for example. However, the query version calculates at refresh and loads like source data, while a DAX column calculates after load. For totals that respond to filters, only a measure works. So overlap exists mainly for row-level columns.
Does a long list of query steps slow down a refresh?
It can, especially when steps stop folding to the source. Once folding breaks, the engine pulls rows across and processes them locally. Still, the number of steps matters less than their type and order. We check which steps fold and then move the costly ones earlier or into the database.
Should I learn Power Query or DAX first?
Start with preparation. Clean, well-shaped tables make every formula shorter, and the skills carry over to Excel. Then learn a handful of measures: sums, a ratio and one time comparison. After that, study filter context properly, since it explains most surprising results.
Who can sort out a report where the two have tangled?
Power BI Agency can. One of our consultants reviews the queries and the measures, then moves each rule to the place where it runs best. The work happens online in your own tenant, with a demo every week. We are a division of Sataware Technologies, independent of Microsoft, and the first assessment is free.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.