Schema drift
Changes to a source's columns over time, such as new, renamed or removed columns, which brittle queries don't handle.
Hard-coded column lists in Changed Type, rename and select steps are the usual casualties. Rule-based steps such as Table.TransformColumnNames, Unpivot Other Columns and MissingField options keep working when columns change, and validation steps can make an unexpected change fail loudly.
In the Power Query editor
Usually discovered as "The column … of the table wasn't found" on refresh, at the Changed Type step.
Learn more
- Dynamic steps that survive new columns
- Combine files whose columns differ
- Unpivot columns so new months are picked up
Practise it
- 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.
- 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.
- 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~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.