Guides

Power Query vs DAX — What Goes Where in Power BI

By Ravindra Bagale, Power BI & Data Analytics trainer · LinkedIn · Published: 30 September 2026 · Last updated: 30 September 2026

Power Query (M) cleans and reshapes tables before they load into the model. DAX calculates measures and columns inside the model. Shape early in Query. Analyse with filter-aware DAX.

Power Query = prepare tables
DAX         = calculate answers under filters

What do I need before this guide?

• You have loaded at least one table (Excel/CSV).
• You know measures vs columns at a basic level (guide).
• Course: What is Power Query?.

Before and after (look at the tables first)

Power Query reshapes before the model (here drop 2024 → 140). DAX then measures what is left under slicers.

Why placement matters (and why you care)

Wrong layer = slow refresh, wrong numbers, or impossible maintenance.

Why you care:

1. Unpivoting 36 month columns with DAX iterators is swimming upstream.
2. Calculating “selected year sales” only with a static Query filter fights interactive reports.
3. Clean Query + clear measures is how teams hand over .pbix files without drama.

Tiny story:

1. Raw file has Mumbai 100 and Pune 40, but Amount is text and City has trailing spaces.
2. Fix that in Power Query.
3. Then write Total Sales = SUM ( Sales[Amount] ) in DAX so a slicer can show Mumbai 100.

Power Query responsibilities (with examples)

What Power Query is

Power Query is the Transform data window. It cleans and reshapes tables before they enter the model. Each click becomes an Applied Step. Refresh replays those steps.

Why use Power Query

1. The file shape is wrong (titles, junk rows, wide month columns).
2. Types are wrong (Amount as text).
3. You need the same clean table every refresh.

What to do there (vertical list)

1. Promote headers and rename columns to clear business names.
2. Set data types (Decimal, Date, Text).
3. Filter out total rows, blank order IDs, test cities.
4. Split “City|State” into two columns.
5. Merge queries (lookup Region from a Cities table).
6. Unpivot month columns into Date + Amount rows.
7. Append monthly CSV files (Folder pattern — course chapter).

Tiny example — what happens to the rows

1. Start: one messy row shows City = "Mumbai " and Amount = "100" (text).
2. Trim City → "Mumbai".
3. Set Amount type → number 100.
4. Same for Pune 40.
5. Close & Apply — the model now has clean numbers ready for SUM.

What you see on screen: Applied Steps on the right (Source, Changed Type, …) and a clean preview grid.

DAX responsibilities (with examples)

What DAX is here

DAX is the formula language inside the model. After Close & Apply, you write measures (and sometimes columns) that answer business questions under filters.

Why use DAX (not Query) for KPIs

1. The answer must change when the reader clicks Mumbai.
2. You need ratios, “sales in 2025”, or comparisons.
3. Query filters at refresh are static — they do not replace interactive measures.

What to do after Close & Apply (vertical list)

1. Total Sales = SUM ( Sales[Amount] ) → overall 140 for Mumbai 100 + Pune 40.
2. Orders = DISTINCTCOUNT ( Sales[OrderId] ).
3. Sales 2025 = CALCULATE ( [Total Sales], Date[Year] = 2025 ) (with a Date table).
4. Margins and ratios that must respond to slicers.
5. Dynamic titles and advanced patterns later in the course.

DAX sees the model — relationships, filter context, measures calling measures.

Decision test (use every time)

Ask one sentence: “Must this change when the reader clicks a slicer?”

1. No — it is about the shape of the raw file → Power Query.
2. Yes — it is an analytical answer → DAX measure.
3. It is a row attribute I will group/filter on → clean in Query if possible; else calculated column.

Worked micro-examples

1. Replace N/A with null in Amount → Query.
2. Show sales for the cities currently selected (Mumbai → 100) → Measure.
3. Create Year from OrderDate for a relationship to Date → preferably Query (or a proper Date table strategy in modelling).
4. Year-to-date sales → DAX (after Date table exists).
5. Unpivot Jan–Dec columns → Query.

What beginners should not do

1. Load dirty sheets “as is” and patch everything with DAX.
2. Create dozens of calculated columns that copy Query logic.
3. Filter the whole fact table in Query to one year, then wonder why the year slicer is empty.
4. Fear Power Query because the formula bar shows M — you can do 80% from the ribbon.

Performance intuition (beginner level)

1. Query work runs at refresh.
2. Measure work runs when visuals query.
3. Heavy row-by-row DAX over huge fact tables can feel slow — keep preparation in Query when it is really preparation.

Decision workshop (copy into Notes)

For each task, write Q or D:

1. Trim spaces in City → Q
2. Sales YTD → D
3. Merge Region from Cities.xlsx → Q
4. % of total sales → D
5. Unpivot month columns → Q
6. Distinct order count → D
7. Replace “NA” with null → Q
8. Same-period last year → D (after Date table)

If you marked any of 1/3/5/7 as D, revisit this guide.

Refresh mental model

1. Click Refresh in Desktop → Query steps run → model tables update → visuals query measures.
2. Confusing Query with DAX usually means confusing refresh time with interaction time.

Tiny check: after refresh, Mumbai is still 100, Pune still 40, and [Total Sales] still returns 140 until a slicer narrows it.

End-to-end mini project (45 minutes)

1. Create a wide Excel with columns: City, Jan, Feb, Mar (Mumbai Jan 100, Pune Jan 40).
2. Get data → Excel → Transform.
3. Select Jan–Mar → Unpivot Columns → rename Attribute to Month, Value to Amount.
4. Set types; Close & Apply.
5. Create [Total Sales] = SUM ( Sales[Amount] ) in DAX.
6. Visualise City vs Amount.
7. Notice: unpivot was Query; total was DAX — that is the whole lesson in one file.

M language fear reduction

1. Every ribbon click writes M in Applied Steps.
2. You can ignore M syntax for weeks and still ship clean tables.
3. Peek at the formula bar later out of curiosity — not on day one.