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
LargeResult
| OrderID | OrderDate | Qty |
|---|---|---|
| A-1 | 2026-10-02 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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).
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;
metarecords are accepted but their metadata isn't stored.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Combining files quiz