Power QueryExercises

Dynamic steps that survive new columns

Replace hard-coded column lists with rules that apply to future columns.

~10 min read · intermediate · Module 19: Refresh robustness and performance concepts

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

Before this: Choose Columns vs Remove Columns, Unpivot columns so new months are picked up

Many UI actions write today's column names into the M code. Changed Type lists every column, Rename lists old and new names, and Unpivot Columns lists the months. When the source adds or renames columns, those steps either break or quietly ignore the change.

Rules instead of lists

Table.TransformColumnNames runs a function on every column name at refresh time, so a column added next month is cleaned too:

let
    Source = #table({" order id", "CUSTOMER name ", "qty"}, {{"A-1", "Ana", 2}})
in
    Table.TransformColumnNames(Source, each Text.Proper(Text.Trim(_)))

Result

Order IdCustomer NameQty
A-1Ana2

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

Computing column lists

Table.ColumnNames reads the current schema, so lists derived from it stay in step with the data. Here every column except Item is a month, and all of them are typed as numbers, including May, which didn't exist when the query was written:

let
    Source = #table({"Item", "Jan", "Feb", "May"}, {{"Kite", "3", "5", "2"}}),
    Keep = {"Item"},
    Months = List.Difference(Table.ColumnNames(Source), Keep),
    Typed = Table.TransformColumnTypes(Source, List.Transform(Months, each {_, Int64.Type})),
    Long = Table.UnpivotOtherColumns(Typed, Keep, "Month", "Units")
in
    Long

Result

ItemMonthUnits
KiteJan3
KiteFeb5
KiteMay2

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

List.Transform(Months, each {_, Int64.Type}) builds the {{"Jan", Int64.Type}, …} list that Changed Type would otherwise hard-code.

Steps that embed today's schema

Generated stepWhat's hard-codedRefresh-safe alternative
Changed Type (after import)Every column nameType only the columns you need, or derive the list
Unpivot Only Selected ColumnsThe month namesUnpivot Other Columns
Expand (merge or JSON)Field names seen in the previewDerive fields from the data, or keep a checked fixed list
Renamed ColumnsOld namesTable.TransformColumnNames with a rule, for systematic renames

Where to find this in Excel

There's no button for this. In Excel you write it in the Power Query editor's Advanced Editor (View > Advanced Editor, also on the Home tab), which shows the whole query as M.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).

Common mistakes

  • Keeping the automatic Changed Type step that lists every source column, so a removed column breaks refresh.
  • Using a rule-based rename that unexpectedly maps two different columns to the same name.
  • Making every step dynamic, including ones that should fail when the schema changes.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop write fixed column lists in most generated steps; the dynamic versions here are formula-bar edits.
  • Turning off automatic type detection (Query Options → Data Load) stops the host from adding a Changed Type step that lists every column.
  • The simulator's held-out refresh tests add, rename or reorder columns, so dynamic steps are what make graded queries pass on new data.
Functions in this lesson:Table.TransformColumnNamesTable.ColumnNamesList.DifferenceTable.TransformColumnTypes

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Clean every column name, including future ones (survey)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.