The editor
Transform data opens Power Query. Four areas: the Queries pane listing every query, the data preview, the Query Settings pane on the right holding Applied Steps, and the ribbon.
Applied Steps is the important one. Every transformation you make is recorded as a step, in order, and each can be renamed, reordered, edited or deleted. That list is the transformation script — and unlike a hand-written one, it cannot drift out of step with what runs.
Step order matters
Steps execute top to bottom. Removing columns before filtering rows is not the same as the reverse, and the difference shows up as errors when a later step references a column an earlier one removed.
The defaults — Changed Type, Filtered Rows, Filtered Rows1 — tell a reader nothing. Right-click → Rename, and a query becomes self-documenting.
Query folding
Query folding means Power Query translates your steps into a single query the source database executes. When it folds, the database does the work and only the result travels.
Some steps break folding — adding an index column, certain custom columns, anything Power Query cannot express in the source's query language. Everything after that point is processed locally on the full data set.
Right-click a step → View Native Query. If it is greyed out, folding stopped at or before that step. Put folding-breaking steps as late as possible.
Power Query or DAX
| Work | Where |
|---|---|
| Cleaning, shaping, type changes | Power Query |
| Combining and reshaping tables | Power Query |
| Row-level columns computed once | Power Query (calculated column) |
| Anything responding to filters and slicers | DAX measure |
The rule of thumb: if the value never changes with what the user selects, compute it in Power Query. If it must respond to the report, it is a measure.