Power QueryExercises

Transform in SQL, Power Query or DAX? Where each step belongs

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

11 min read

Put a step as far upstream as it can sensibly go: in the SQL source when it is shared logic the database can do, in Power Query when it shapes data for this model, and in DAX only when the result depends on the filters a report user picks. Row-by-row work is upstream work; anything that has to respond to slicers, such as a ratio or a year-to-date total, is a DAX measure.

The short answer

Ask two questions about each step.

  1. Does the result depend on what the report user has filtered? If yes, it is a DAX measure. Microsoft describes measures as results that "change as you interact with your reports" (Microsoft Learn: measures in Power BI Desktop). A share of total, a margin percentage or a year-to-date figure cannot be stored in advance, because nobody knows which rows will be in view.
  2. If not, how far upstream can it go? Fixed, row-level work (cleaning text, splitting columns, mapping codes to categories, removing rows, joining a lookup) belongs in Power Query or in the source. Choose the source when the logic is shared beyond this model or the database does it much faster; otherwise Power Query.

The rest of this post fills in the details behind those two questions. It assumes Import mode, the default for most models; in DirectQuery, Microsoft's folding guidance says every Power Query step must fold, which pushes even more of the work to the source.

Where steps belong: shared heavy logic in the SQL source, fixed row-level work in Power Query, filter-dependent results as DAX measures

Where each kind of step belongs

StepSQL source (view or table)Power QueryDAX
Filter out test, cancelled or old rowsYes, if every consumer wants them goneYes, and it folds to SQL sourcesOnly as a report filter, not to remove data
Clean text, fix types, trim keysIf the warehouse owns data qualityYes, the usual placeNo
Join a lookup or mapping tableYes, for large tablesYes, with a mergeUse a relationship instead of a join
Row-level arithmetic (margin = amount − cost)YesYesCalculated column only if it needs model data
Summarise to a coarser grainYesYes, with Group ByRarely; measures aggregate at query time
Ratios, shares, averages of totalsOnly for a fixed, pre-agreed groupingOnly for a fixed, pre-agreed groupingYes, as a measure
Running totals and time intelligence for report filtersNoOnly for a fixed, pre-agreed cutYes, as a measure
Unpivot, fill down, reshape messy exportsPossible but verboseYesNo

Two patterns stand out. Upstream of the model, SQL and Power Query overlap a lot, and the choice is about ownership and speed. Downstream, DAX does a job neither can do, because measures are evaluated when a visual asks for them. The M vs DAX guide covers the Power Query and DAX boundary in more depth.

When the SQL source is the right place

Microsoft's guidance for Import models is to "delegate as much processing to the data source as possible", and, where steps can't fold, to consider preparing the data in the source: a view that transforms it, or data prepared in advance such as a data warehouse (Microsoft Learn: query folding guidance). Choose SQL when:

  • Other tools need the same logic. A view is readable by Power BI, Excel, Python and anything else that speaks SQL. If the team works in Python, Power Query vs Python pandas compares the cleaning side. Logic in a Power Query file is only in that file.
  • The table is large and the step is a filter, join or aggregation. Databases are built for these, and only the result travels to Power BI.
  • A database team owns the definitions. If "active customer" is defined in the warehouse, reuse it; do not redefine it in every report.

You do not always have to write SQL to get this benefit. When Power Query steps fold, Power Query writes the SQL for you. A filter like this one, placed straight after the navigation step on a SQL source, is sent to the database as a WHERE clause:

let
    Orders = #table(
        type table [OrderID = Int64.Type, Status = text, Amount = number],
        {{1, "Complete", 120}, {2, "Cancelled", 80}, {3, "Complete", 45}, {4, "Test", 1}}
    ),
    Completed = Table.SelectRows(Orders, each [Status] = "Complete"),
    Kept = Table.SelectColumns(Completed, {"OrderID", "Amount"})
in
    Kept

Result

OrderIDAmount
1120
345

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

Orders 1 and 3 remain, with only the OrderID and Amount columns. Against a database, both steps fold, so the database does the filtering and column selection. The query folding lesson explains which steps fold, and the SQL to Power Query M guide maps common SQL clauses to their M equivalents.

If you write the SQL yourself

When a step can't fold, Microsoft suggests a native SQL query and recommends passing parameters with Value.NativeQuery. The same page warns that later Power Query steps won't fold after a native query, so the query should hold as much of the logic as possible (Microsoft Learn: query folding guidance). For SQL Server and a few other connectors there is an exception: passing [EnableFolding = true] to Value.NativeQuery keeps folding active for the steps after it (Microsoft Learn: query folding on native queries). For incremental refresh, the folding guidance warns that a native query makes the engine read every row, while Microsoft's incremental refresh overview shows a native query working when RangeStart and RangeEnd are written into the SQL itself; the incremental refresh post covers that case.

let
    Source = Sql.Database("sql-server-name", "SalesDW"),
    Orders = Value.NativeQuery(
        Source,
        "SELECT OrderID, Amount FROM dbo.Orders WHERE Status = @status",
        [status = "Complete"]
    )
in
    Orders
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

The same page reminds you to use an account with read-only permission, because a native query can run any statement, including ones that change data.

When Power Query is the right place

Power Query is the right place for shaping that is specific to this model, or that SQL handles badly:

  • Cleaning exports and files. Promoting headers, removing report banners, unpivoting month columns and filling down are natural in Power Query and awkward in SQL. Files and folders have no SQL engine to push work to anyway.
  • Combining sources. A spreadsheet of targets joined to a database table, or several CSV files appended into one table.
  • Row-level columns. Microsoft's data reduction guidance says it is less efficient to add columns as DAX calculated columns than as Power Query columns, because calculated columns typically compress less well and are built after all Power Query tables load (Microsoft Learn: data reduction techniques).

A margin column is a good example. Each row's margin depends only on that row, so it can be computed once at refresh:

let
    Sales = #table(
        type table [Region = text, Amount = number, Cost = number],
        {{"North", 1000, 600}, {"North", 100, 90}, {"South", 400, 200}}
    ),
    WithMargin = Table.AddColumn(Sales, "Margin", each [Amount] - [Cost], type number),
    WithPct = Table.AddColumn(WithMargin, "MarginPct", each [Margin] / [Amount], type number)
in
    WithPct

Result

RegionAmountCostMarginMarginPct
North10006004000.4
North10090100.1
South4002002000.5

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

The margins are 400, 10 and 200. The MarginPct column (0.4, 0.1 and 0.5) is correct for each single row, which is exactly why it is dangerous, as the next section shows. The conditional columns lesson covers row-level logic with if … then … else.

When DAX is the only right place

Some numbers are not properties of a row; they are properties of whatever set of rows is currently in view. Ratios are the classic case.

The ratio trap

Suppose the report shows margin percentage by region. If you average the per-row percentages from Power Query, North's two rows give 40% and 10%, averaging 25%. But North actually earned 410 margin on 1,100 of sales, which is about 37.3%.

let
    Sales = #table(
        type table [Region = text, Amount = number, Cost = number],
        {{"North", 1000, 600}, {"North", 100, 90}, {"South", 400, 200}}
    ),
    WithPct = Table.AddColumn(Sales, "MarginPct", each ([Amount] - [Cost]) / [Amount], type number),
    North = Table.SelectRows(WithPct, each [Region] = "North"),
    Compare = #table(
        {"Method", "North margin %"},
        {
            {"Average of row percentages", Number.Round(List.Average(North[MarginPct]) * 100, 1)},
            {"Total margin / total amount", Number.Round((List.Sum(North[Amount]) - List.Sum(North[Cost])) / List.Sum(North[Amount]) * 100, 1)}
        }
    )
in
    Compare

Result

MethodNorth margin %
Average of row percentages25
Total margin / total amount37.3

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

The simulator returns 25 for the average of row percentages and 37.3 for total margin divided by total amount. The small order counts as much as the large one in the first method, which is rarely what anyone means by "margin %".

You could compute the correct 37.3% in Power Query with a Group By, but only for the grouping you chose in advance. The moment a user filters to one product or one month, the stored figure is wrong again. A DAX measure divides the totals for whatever is in view:

Margin % = DIVIDE ( SUM ( Sales[Amount] ) - SUM ( Sales[Cost] ), SUM ( Sales[Amount] ) )

So the split is: Power Query supplies clean Amount and Cost columns; DAX does the division at query time. The same reasoning applies to shares of total, averages per customer, distinct counts across a filtered range, year-to-date and prior-year comparisons. To practise the fixed-grouping version, try showing each region's share of total revenue: it builds the share for one grouping, by region, in Power Query, which is correct only as long as nobody filters the result.

DAX calculated columns: the narrow case

A DAX calculated column is evaluated per row and, in Import mode, stored at refresh, like a Power Query column (Microsoft Learn: calculated columns). Microsoft's data reduction guidance (Microsoft Learn: data reduction techniques) says it can still be the better choice when the formula evaluates measures or needs modelling functions only DAX has, such as parent-child hierarchy functions. Otherwise, add the column in Power Query.

A worked split for one sales model

Here is how the steps of a typical sales model divide up:

StepPlacementReason
Exclude test and cancelled ordersSQL view, or a folding Power Query filterShared rule; database does it fastest
Join product category from the product tableModel relationshipKeeps both tables usable; no need to widen the fact table
Trim and upper-case customer codesPower QueryCleaning for this model
Map region codes to region namesPower Query merge with a mapping tableFixed, row-level
Margin per rowPower Query columnRow-level, fixed
Margin %DAX measureRatio that depends on the filter
Year-to-date salesDAX measureDepends on the date in view

If a second model needs the same cleaned tables, the Power Query rows move upstream again, into a view or a dataflow. The Desktop or dataflows comparison covers that decision.

Frequently asked questions

Is Power Query slower than SQL?

For filters, joins and aggregations on a large database table, doing the work in the database is usually faster, and folding gives you that without writing SQL. For files, spreadsheets and small lookups, the choice makes little difference. Microsoft's folding guidance (Microsoft Learn: query folding guidance) says best refresh performance for an Import table on a relational source comes from making sure the query folds.

Can I avoid DAX altogether?

For fixed tables, yes: everything can be calculated in Power Query and loaded as columns. As soon as a report needs a ratio or a total that responds to slicers, you need a measure. Even a simple sum in a visual is an aggregation the model performs when the visual asks for it.

Should I move my calculated columns to Power Query?

Usually, if they only use values from the same row or from a lookup Power Query can merge. Keep them in DAX when they depend on measures or model-only functions. Moving them can also reduce model size; the file size post covers that side.

Practise the upstream half

The skill that makes this decision easy is writing Power Query steps that do one clear job each, so you can see which ones could move to SQL and which belong in the model. Practise row-level logic and fixed groupings in the group by and aggregation exercises, then compare your query with the query folding diagnosis guide to see which steps would reach the database. Power Query Exercises runs each check in the browser, so you can test the M side of the split before touching a model.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

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.