1.3 — Create and Modify Parameters
PL-300 Domain 1 · Skill 1.3
What You Need to Know
A parameter is a named value that Power Query steps can use in place of a hard-coded value. Parameters make it easy to point the same report at another server, filter a large source, or let the service change a value without editing the file.
Creating a parameter
In the Power Query Editor, choose Home > Manage Parameters > New Parameter and set:
| Setting | Purpose |
|---|---|
| Name and Description | How the parameter appears in steps and in the service |
| Required | Whether a value must be given |
| Type | Text, Decimal number, Date/Time, Date and so on. The value must match the type |
| Suggested values | Any value, a List of values (a dropdown), or values from a Query |
| Current value | The value used now |
A parameter shows up as its own query with a parameter icon. You can also convert a query that returns a single value into a parameter.
Common uses
- Switch environments: replace the server and database names in the Source step with
ServerNameandDatabaseName. Changing the parameter moves the whole report from Test to Production. - Filter rows: in Filter Rows, choose the parameter icon next to the value box instead of typing a value.
- File paths and URLs: build a path such as
RootFolder & "Sales.xlsx". - Incremental refresh: needs two Date/Time parameters named exactly RangeStart and RangeEnd, used to filter the date column. The service then replaces them with each partition's range.
- Dynamic M query parameters: in DirectQuery, bind a parameter to a model column so a slicer selection is passed into the source query.
Changing parameter values
- Desktop: Transform data > Edit parameters (no need to open the Power Query Editor), or edit the parameter query.
- Service: semantic model Settings > Parameters. Only parameters of supported types appear, and changing one changes the data on the next refresh.
- Deployment pipelines: a parameter rule sets a different value per stage, such as the Production database name.
Templates
Saving the report as a Power BI template (.pbit) stores the queries, model and report without data. Opening it prompts for the parameter values, so one template can serve several regions or clients.
Key Concept: Use parameters wherever a value differs between environments, users or refresh windows. Change them in the service or with deployment rules, so nobody has to edit and republish the .pbix.
Exam Traps
- Naming incremental refresh parameters anything other than RangeStart and RangeEnd, or giving them the Date type instead of Date/Time
- Republishing the report just to change a server name that should be a parameter
- Expecting a parameter's type to be checked loosely (a Text value in a Date parameter fails)
- Confusing Power Query parameters with what-if (numeric range) parameters in the report, which create a DAX table and a slicer
Check your understanding
Practice Scenario
A report connects to a development SQL Server while it's built and must connect to the production server after it's published. You must be able to switch servers without editing and republishing the .pbix file. What should you do?
Log in to save your progress.
Sources
- Dynamic M query parameters in Power BI Desktop, Microsoft Learn
- Parameters in Power Query, Microsoft Learn
- Configure incremental refresh, Microsoft Learn
- Create and use Power BI templates, Microsoft Learn