Power Query parameters
For data connection and transformation, Power BI uses a technology called Power Query. Power Query is a key component of Power BI, but it also powers data connection and data shaping in Excel. Power Query provides extensive capabilities, including the ability to create and use parameters that help Power Query become more extensible and maintainable. Power Query parameters allow dynamic values to be used that can be used in a query or queries.
The Manage Parameters tool is used from within the Power Query window (Home menu | Transform data) and allows the creation of new parameters to be used within Power Query. Each parameter has these values:
- Name.
- Description.
- Required or Not Required.
- Type – This is where the data type is set (decimal number, date/time, text, or binary).
- Suggested Values – This is either Any value, List of Values, or Query.
- Current Value – Allows for the initial value of the parameter to be set.
We will check out the Suggested Values parameter in depth here.
Any Value
A parameter value can be any string of characters or numbers the user desires to enter.
List of Values
This option provides the capability of defining a list of values that can be used as parameters. The user interface shows a table interface to help the user enter the values to be stored as preset options. When List of Values is used, the user also selects Default Value and Current Value from the options defined in the List of Values table. It is possible to type values in as a parameter; List of Values merely provides the default options that can be easily selected.
Query
This option makes it possible to select a query that contains an output list of values. This is useful when you need to have a dynamically updating list of values and you don’t want to hardcode them into the Manage Parameters tool. Instead, these could be stored in a database or a file and then included in the data model as a typical query. To use values in a parameter, a list query first needs to be made. To make a list query inside Power Query, take the following steps:
- Select the column from an existing query that contains the list of values you want to use as the dynamic list of values in a parameter.
- Right-click the column and select Add as New Query.
- A new query is created in the query list. The icon used is different from the typical table-like icon. Instead, it looks more like a list.
- The new query can be renamed as needed and then selected as an option in the Query drop-down list.
When a query is used for Suggested Values, the option for Current Value is still available.
Parameters are often used in different ways, but common scenarios include the following:
- A common value used for multiple transformations
- Used as arguments for custom functions
When parameters are configured, they will become a selectable option on transforms such as Filter Rows. Always Allow may need to be checked under the View menu | Parameters.
