📌 Stage 3 · Automate · Power Query · Query parameters · 35–45 min

Change the month in one place, watch the whole model follow

Learning goals

  • Create the four parameters Northgate's queries depend on
  • Reference a parameter inside a filter step and inside a custom column
  • Explain how the Settings sheet mirrors the parameters for the formula layer
  • Demonstrate the ripple: move the close date and watch dependent formulas follow

Concepts

Four numbers that run the report

A parameter is a named value a query reads instead of a constant typed inside a step. Northgate maintains four: SourceFolder (text, C:\Reports\Exports), ReportMonth (whole number, 8), ReportYear (whole number, 2026), and MonthlyCapacity (whole number, 150). Create them under Data > Get Data > Query Parameters, or inside the editor's Manage Parameters dialog.

The payoff is that September is not a rebuild. Change ReportMonth from 8 to 9 and every query filtered on it -- Utilization's month filter, the aging cutoff's end-of-month -- moves with it. The alternative, editing the same literal inside three queries, is how a workbook ends up reporting September hours against an August cutoff.

💡 Tips:

  • Give parameters a description in the Manage Parameters dialog; it shows up as a tooltip wherever the parameter is referenced, which is free documentation.
  • A parameter used only once is still worth it: the name documents the intent, and the second use always arrives.

Parameters and the Settings sheet

The workbook keeps a second copy of the same numbers on the Settings sheet, because worksheet formulas cannot read query parameters directly: period start in C6, close date in C7, capacity in C10, month number in C11, source folder in C12. The query layer reads the parameters; the formula layer reads Settings. They agree by convention -- the Query Log notes the pairing, and the close routine (lesson 12) checks it.

This mirror is also what makes the browser exercise work: the Settings cells are live inputs, and every formula that references them recalculates when you edit one. It is the same ripple a parameter drives in the real workbook, made visible.

=Settings!$C$10*COUNTA(Utilization!$B$5:$B$1004)

Team capacity for August: the 150-hour monthly capacity from Settings times the number of consultants the Utilization query returned (COUNTA counts the seven names). 150 x 7 = 1,050 available hours -- the denominator of every utilization number in the course.

=IF(MONTH($D5)=MONTH(Settings!$C$7),"Yes","No")

A filter expressed as a formula: compare each row's week to the month of the close date in Settings!$C$7. The Utilization query does the same comparison with the ReportMonth parameter -- two layers reading two copies of one decision.

Reference a parameter inside a step

In the editor, a filter step becomes parameter-driven the moment you type the parameter's name where a literal was: Date.Month([Week Start]) = ReportMonth. The custom column in lesson 7's aging logic does the same with Date.EndOfMonth(#date(ReportYear, ReportMonth, 1)) -- the cutoff is derived from the parameters, never typed as a date.

One caution from hard experience: parameters make refresh order matter. Queries that reference other queries refresh downstream-first automatically; a workbook that also pulls Settings from a query needs that chain intact. Keep the dependency graph shallow -- parameters, then connections, then transforms -- and Refresh All will never surprise you.

💡 Tips:

  • Never hard-code a path in two places. If a query needs the folder, it reads SourceFolder; if a formula needs it, it reads Settings. One fact, one address per layer.

Practice

The exercise opens on Settings, the sheet the formula layer reads. You will edit a value, watch the ripple, then reset. Nothing is saved in the browser.

  1. Read the parameter mirror — Settings C7 is Aug 31, 2026 (the close date the ReportMonth and ReportYear parameters imply), C10 is 150 (MonthlyCapacity), C11 is 8 (ReportMonth), and C12 is C:\Reports\Exports (SourceFolder). Cross-check the M Code Reference sheet: the four parameter queries hold the same values.
  2. Move the close date — Edit Settings C7 to Sep 30, 2026 and press Enter. Clean Pipeline J5 (Age Days) jumps from 56 to 86; Invoice Facts M8 stays Overdue and three more open invoices flip with it (INV-2407 due Sep 11, INV-2409 due Sep 18, INV-2410 due Sep 23); the Monthly Report's period metrics stretch to include INV-2413.
  3. Watch the boundary rows move — With the close at Sep 30, the aging comparison in column N's source logic shifts too: INV-2408, paid Sep 3, now sits inside the period and Collected in period includes its 1,860.00. One cell changed, and every sheet that reads Settings recalculated.
  4. Reset — Click Reset Data above the spreadsheet to restore Aug 31, 2026, then confirm Clean Pipeline J5 is 56 again. In your live workbook the equivalent act is changing ReportMonth to 9 and refreshing -- never editing individual formulas.

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:

  • ☐ Edited Settings C7 to Sep 30, 2026 and observed Age Days, status, and period metrics recalculate
  • ☐ Reset the data and confirmed Clean Pipeline J5 returned to 56 days
  • ☐ Verified the baseline: capacity of 150 hours per consultant (1,050 for the team) feeding every utilization number