📌 Stage 4 · Document and Run · Power Query · Documentation · 35–45 min

Renamed steps, M comments, and the Query Log your successor will read

Learning goals

  • Rename Applied Steps so a stranger can read the query's story
  • Add descriptions to steps and comments to M code
  • Maintain the Query Log as the workbook's table of contents
  • Paste a script from the M Code Reference sheet into a blank query's Advanced Editor

Concepts

The Applied Steps list is the documentation

Every click in the Power Query editor appends a step with a machine-generated name -- Promoted Headers, Changed Type, then Changed Type1, Changed Type2. Six months later nobody, including you, can tell which of those three typed the dates. Right-click a step > Rename and give it the name of the decision it records: DatesUK, AmountNoDollar, RegionKY. Right-click > Properties adds a description that shows as a tooltip on the step.

The test Northgate applies: read the Applied Steps list top to bottom and say, out loud, what this query does. If you cannot, the query is not done -- no matter that it works. The Clean Pipeline script on the M Code Reference sheet passes that test; the same query with default names would not.

💡 Tips:

  • Rename steps the day you create them. Retroactive renaming is archaeology, and archaeology does not happen.
  • Step descriptions survive copy-paste between workbooks; tribal memory does not.

Reading and editing M in the Advanced Editor

View > Advanced Editor shows the whole query as one M script: a let expression whose steps are named assignments ending in an in. This is where the M Code Reference sheet earns its keep -- each row holds a query's full script, and pasting one into a blank query (Data > Get Data > From Other Sources > Blank Query, then Advanced Editor) rebuilds that query in your own workbook in seconds.

M comments start with two slashes and are free. Use them for the decisions a step name cannot hold: why the locale is en-GB, why one row was expected to disappear, why capacity is 150. Comments are also the honest place to record what you did NOT do -- 'left n/a rows in, count them monthly' is documentation gold.

=COUNTA('Query Log'!$B$5:$B$1004)

Twelve queries and parameters are logged. If your own workbook's Queries & Connections pane shows a count that disagrees with this, you have an undocumented query -- the exact failure mode the log exists to prevent.

The Query Log: a table of contents for the workbook

The Query Log sheet lists every query with its kind (Parameter, Connection, or Transform), where it loads, its position in the refresh order, a one-line purpose, an owner, and a last-reviewed date. It is deliberately a plain sheet and not a clever formula, because its reader is a person on a bad day: the operations lead out sick, the accountant asking where a number came from, you in eighteen months.

Two habits keep it true. Update the row whenever a query's purpose changes, not at some annual review. And when a query is deleted, delete its row -- a log that overstates the workbook is worse than none.

💡 Tips:

  • Give the M Code Reference sheet the same discipline: one row per query, script kept in step with the live query. The moment they drift, the sheet becomes a rumor.
  • Group queries in the Queries & Connections pane (staging, transforms, parameters) to match the log's order; navigation is documentation too.

Practice

The exercise opens on the Query Log. The M Code Reference tab holds the scripts the log points at. Nothing is saved in the browser.

  1. Read the log — Twelve rows: four parameters, three connections, five transforms. Refresh Order runs 1 to 12 -- parameters first, then staging connections, then the transforms that depend on them. Owners are people (M. Reyes, D. Whitfield, S. Lindgren), and every row was last reviewed Aug 31, 2026.
  2. Match a log row to its script — Take row 14, Invoice Facts. On M Code Reference, find the Invoice Facts row and read its script: the trim and upper-case, the Table.NestedJoin against Service Lines, the en-GB date typing, the NET 30 custom column, the aging conditional column. Every step name is a decision you made in lessons 3, 6, and 7.
  3. Read a script for a stranger — Read the Clean Pipeline script out loud, step name by step name: Distinct, Trimmed, RegionOH, RegionOhLower, RegionMI, RegionIN, RegionKY, AmountNoDollar, AmountNoComma, AmountTyped, NoErrorRows. That is a story a successor can follow without asking you anything.
  4. Rebuild one query in your own workbook — In desktop Excel: Data > Get Data > From Other Sources > Blank Query, open Advanced Editor, paste the Service Lines script, and rename the query Service Lines. It is the shortest script on the sheet and the one every other transform leans on.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Confirmed the Query Log lists 12 queries with kind, load destination, refresh order, owner, and review date
  • ☐ Matched at least one log row to its full script on the M Code Reference sheet
  • ☐ Verified the baseline: 12 rows logged (4 parameters, 3 connections, 5 transforms) against the same 12 scripts