Power QueryExercises

Refresh-proof Power Query: a 15-point checklist

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

Jump to the practice tasks

Rows

  1. Filters describe values, never positions (no 'remove rows 3–5').
  2. Banner and footer rows are removed by content, not by a fixed count.
  3. Top-N steps sort first and have a unique tie-breaker.
  4. Exclusion filters (<> "Cancelled") handle null deliberately.

Columns

  1. Choose vs Remove Columns matches what a new column should do.
  2. Reshaping uses Unpivot Other Columns, not a fixed list of months.
  3. Systematic header cleaning uses a rule (Table.TransformColumnNames), not a list of renames.
  4. Required columns fail loudly; only genuinely optional ones use MissingField.UseNull or Ignore.

Values

  1. Types are set with the source's culture.
  2. Text keys are trimmed and case-standardised before merges, groups and deduplication.
  3. Mappings use a table (merge) with a visible default for unknown codes, not a hard-coded if-chain that silently misses new ones.
  4. Error cells are handled where they're created, and counted.

Sources

  1. Folder queries filter by extension and name pattern.
  2. Paths, dates and thresholds live in parameters, not in steps.
  3. 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
    Long

Result

ItemMonthUnits
KiteJan3
KiteFeb5
KiteMar2
ReelJan1
ReelMar4

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

A padded header, a new Notes column and a new month are all handled: Notes is excluded by rule, Mar is included by rule.

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.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

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.