Power QueryExercises

Parameters: one value, many queries

Replace repeated hard-coded values with parameters that queries reference.

~9 min read · intermediate · Module 15: Combining files and parameters

By Tahir Nas · Examples run in our simulator · Updated

Before this: Combine files from a folder

A report date typed into three queries is three places to update at month end, and one of them will be missed. A parameter is a small query that holds one value. Other queries refer to it by name, so changing it changes every query that uses it.

Referencing a parameter

Here two parameters, ReportDate and MinQty, are separate queries. The main query uses them like any other name:

Query ReportDate

#date(2026, 10, 31) meta [IsParameterQuery = true, Type = "Date", IsParameterQueryRequired = true]

Query MinQty

3 meta [IsParameterQuery = true, Type = "Number", IsParameterQueryRequired = true]
let
    Orders = #table({"OrderID", "OrderDate", "Qty"}, {
        {"A-1", #date(2026, 10, 2), 5},
        {"A-2", #date(2026, 10, 20), 1},
        {"A-3", #date(2026, 11, 3), 8},
        {"A-4", #date(2026, 9, 28), 4}
    }),
    InPeriod = Table.SelectRows(Orders, each [OrderDate] > Date.AddDays(ReportDate, -30) and [OrderDate] <= ReportDate),
    Large = Table.SelectRows(InPeriod, each [Qty] >= MinQty)
in
    Large

Result

OrderIDOrderDateQty
A-12026-10-025

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

Thirty days up to 31 October, quantity 3 or more. Change ReportDate to 30 November and the same query reports November.

The meta [IsParameterQuery = true, …] record is what Manage Parameters writes. It tells the host to show the query in the parameters dialog with a type and a required flag; the value itself is the part before meta.

Good candidates

  • The report date or period end, instead of DateTime.LocalNow(), so a report can be rerun for a past month.
  • Thresholds and business rules that people argue about (minimum quantity, tolerance).
  • Folder paths and server names that differ between your machine, a colleague's and the published version.
  • The environment: test vs production database names.

Parameters and refresh

A fixed report date makes refreshes reproducible: rerunning October's report next week gives October's numbers. If the report should always show 'the last 30 days', use a calculated parameter (a query computing the date) and accept that the output changes daily.

Where to find this in Excel

Excel for Windows
Power Query editor > Home > Manage Parameters > New Parameter
What you see
You set a name, type and current value. The parameter is a query of its own, listed with your other queries.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Create a parameter query (Power Query) (Microsoft Support); Parameters (Microsoft Learn).

Manage Power Query queries in Excel: load, refresh and edit

Common mistakes

  • Typing the same date or path into several queries instead of referencing one parameter.
  • Using DateTime.LocalNow() for a period end, so last month's report can't be reproduced.
  • Storing credentials in parameters or query text.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Home → Manage Parameters → New Parameter. In Power BI, parameters can also be changed in the service's dataset settings after publishing.
  • Excel users sometimes read a parameter from a named cell with Excel.CurrentWorkbook(); that works only in Excel, and the simulator doesn't run it.
  • In the simulator, a parameter is an ordinary query in the workspace; meta records are accepted but their metadata isn't stored.
Functions in this lesson:Table.SelectRowsDate.AddDays

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Combining files quiz