Refresh-proof Power Query: a 15-point checklist
Checklist · By Tahir Nas · Examples run in our simulator · Published · Updated
Rows
- Filters describe values, never positions (no 'remove rows 3–5').
- Banner and footer rows are removed by content, not by a fixed count.
- Top-N steps sort first and have a unique tie-breaker.
- Exclusion filters (
<> "Cancelled") handle null deliberately.
Columns
- Choose vs Remove Columns matches what a new column should do.
- Reshaping uses Unpivot Other Columns, not a fixed list of months.
- Systematic header cleaning uses a rule (Table.TransformColumnNames), not a list of renames.
- Required columns fail loudly; only genuinely optional ones use MissingField.UseNull or Ignore.
Values
- Types are set with the source's culture.
- Text keys are trimmed and case-standardised before merges, groups and deduplication.
- Mappings use a table (merge) with a visible default for unknown codes, not a hard-coded if-chain that silently misses new ones.
- Error cells are handled where they're created, and counted.
Sources
- Folder queries filter by extension and name pattern.
- Paths, dates and thresholds live in parameters, not in steps.
- A check query reports row counts and the latest date after each refresh.
Test it with tomorrow's data
The fastest way to run the checklist is to change the input and rerun. Add a column, add a category, add a blank row, and see what happens. A query built from rules passes without edits:
let
Tomorrow = #table({" Item ", "Jan", "Feb", "Mar", "Notes"}, {{"Kite", 3, 5, 2, "new column"}, {"Reel", 1, null, 4, null}}),
Headers = Table.TransformColumnNames(Tomorrow, Text.Trim),
Months = List.Select(Table.ColumnNames(Headers), each List.Contains({"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, _)),
Long = Table.UnpivotOtherColumns(Table.SelectColumns(Headers, {"Item"} & Months), {"Item"}, "Month", "Units")
in
LongResult
| Item | Month | Units |
|---|---|---|
| Kite | Jan | 3 |
| Kite | Feb | 5 |
| Kite | Mar | 2 |
| Reel | Jan | 1 |
| Reel | Mar | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
- 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~8 minPro
Unpivot month columns into rows (hardware)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- intermediate~12 minPro
Combine every monthly CSV in a folder (kite shop)
Read every file in the folder, keep the file name, and stack the orders into one typed table.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Best practices when working with Power Query (Concept, Microsoft Learn)
- Data types in Power Query (Concept, Microsoft Learn)
- Dealing with errors in Power Query (Concept, Microsoft Learn)
- Table.TransformColumnNames (Function reference, Microsoft Learn)
- List.Select (Function reference, Microsoft Learn)
- Table.ColumnNames (Function reference, Microsoft Learn)
- List.Contains (Function reference, Microsoft Learn)
- Table.UnpivotOtherColumns (Function reference, Microsoft Learn)
- Table.SelectColumns (Function reference, Microsoft Learn)
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.