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 Id | Customer Name | Qty |
|---|---|---|
| A-1 | Ana | 2 |
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
LongResult
| Item | Month | Units |
|---|---|---|
| Kite | Jan | 3 |
| Kite | Feb | 5 |
| Kite | May | 2 |
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 step | What's hard-coded | Refresh-safe alternative |
|---|---|---|
| Changed Type (after import) | Every column name | Type only the columns you need, or derive the list |
| Unpivot Only Selected Columns | The month names | Unpivot Other Columns |
| Expand (merge or JSON) | Field names seen in the preview | Derive fields from the data, or keep a checked fixed list |
| Renamed Columns | Old names | Table.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.
Table.ColumnNamesList.DifferenceTable.TransformColumnTypesNext 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.
- intermediate~9 minPro
Clean every column name, including future ones (survey)
Trim and capitalise all column headers with one dynamic step instead of renaming each column by hand.
- intermediate~9 minPro
Clean every column name, including future ones (timesheet)
Trim and capitalise all column headers with one dynamic step instead of renaming each column by hand.
- advanced~10 minPro
Set types without failing when an optional column is missing (kite shop)
A Changed Type step that lists Discount breaks when an export has no Discount column. Type only the columns that are present.
- advanced~10 minPro
Set types without failing when an optional column is missing (bakery)
A Changed Type step that lists Discount breaks when an export has no Discount column. Type only the columns that are present.
- advanced~10 minPro
Set types without failing when an optional column is missing (bike rental)
A Changed Type step that lists Discount breaks when an export has no Discount column. Type only the columns that are present.
- intermediate~9 minPro
Fix a query that names columns by position (kite shop)
The starter skips the header line and renames Column1…Column4. When the export's column order changes, values land in the wrong columns.
- intermediate~9 minPro
Fix a query that names columns by position (bakery)
The starter skips the header line and renames Column1…Column4. When the export's column order changes, values land in the wrong columns.
- intermediate~9 minPro
Fix a query that names columns by position (bike rental)
The starter skips the header line and renames Column1…Column4. When the export's column order changes, values land in the wrong columns.
- intermediate~8 minPro
Keep the last seven days of data, relative to the data (kite shop)
Keep orders from the 7 days ending on the latest OrderDate in the export, so the query rolls forward on every refresh.
- intermediate~8 minPro
Keep the last seven days of data, relative to the data (bakery)
Keep orders from the 7 days ending on the latest OrderDate in the export, so the query rolls forward on every refresh.
- intermediate~8 minPro
Keep the last seven days of data, relative to the data (bike rental)
Keep orders from the 7 days ending on the latest OrderDate in the export, so the query rolls forward on every refresh.
- intermediate~7 minPro
Accept both old and new names for a column (kite shop)
Older exports call the column Quantity, newer ones Qty. Always end up with Qty, whichever arrives.
- intermediate~7 minPro
Accept both old and new names for a column (bakery)
Older exports call the column Quantity, newer ones Qty. Always end up with Qty, whichever arrives.
- intermediate~7 minPro
Accept both old and new names for a column (bike rental)
Older exports call the column Quantity, newer ones Qty. Always end up with Qty, whichever arrives.
- advanced~10 minPro
Switch the source query with a parameter (kite shop)
The same report runs for North or South. A Settings row says which; pick the matching source query without editing the steps.
- advanced~10 minPro
Switch the source query with a parameter (bakery)
The same report runs for North or South. A Settings row says which; pick the matching source query without editing the steps.
- advanced~10 minPro
Switch the source query with a parameter (bike rental)
The same report runs for North or South. A Settings row says which; pick the matching source query without editing the steps.
- advanced~13 minPro
Fill in missing store–month combinations with zero (kiosks)
Stores with no sales in a month have no row at all, so charts skip them. Produce every store × month pair, with 0 where there were no sales.
- advanced~13 minPro
Fill in missing store–month combinations with zero (pop ups)
Stores with no sales in a month have no row at all, so charts skip them. Produce every store × month pair, with 0 where there were no sales.
- advanced~14 minPro
Debug a query that works today and breaks on refresh (retail)
The starter passes on this month's sheet but fails next month: it removes a column that may vanish and unpivots a fixed month list.
- advanced~14 minPro
Debug a query that works today and breaks on refresh (franchise)
The starter passes on this month's sheet but fails next month: it removes a column that may vanish and unpivots a fixed month list.