Introduction
Query Parameters allow users to easily make parts of reports and data models based on one or more parameter values. By attaching Query Parameters to a report, the user may render the query elements based on those parameters that were generated.
Creating Parameters
Open Power Query Editor by clicking on ‘Transform data’ at the ‘Home’ tab of ‘Power BI Desktop’.
In the ‘Power Query Editor’ window, from the ‘Home’ tab select ‘Manage Parameters’ to bring up the ‘Parameters’ dialog, as shown below
The new ‘Manage Parameters’ dialog will open and it contains the following elements that allow you to build new parameters
- Name
Includes the parameter by name. - Description
Once parameter information is displayed, the definition is displayed next to the parameter name. - Required
The checkbox determines whether users may define a value that needs to be given for the parameter. - Type
Applies a limitation on data form to the input value for the parameter - Suggested values
You can further limit entries that users can choose for a given parameter or provide for that. When specifying the parameter value, users can select one of the available values (Any values, List of values, Query). - Default value
Sets a parameter with the default value. Present value: Defines the value of the parameter for the current value.
Let see how to create a parameter. I described all the above terms, so now I mention all these fields into the ‘Manage Parameter’ window.
Here, I am creating the parameter for the database that will be used to add database tables in Power BI. We have multiple databases in our SQL server, so we will connect our database through parameters.
You can check my article on connecting SQL server in Power BI.
Now I am providing the values of the above segments.
- Name
In this segment, I will provide the name of the parameter i.e. ‘Database’ - Description
I will use this parameter to connect the database so that my description is relevant to my task. - Required
Click the checkbox of this segment. - Type
I will take the ‘Text’ type parameter - Suggested Values
I will select the ‘List of values’ and give the name of my database. - Default value
I will choose the first database value as my default value. - Current Value
Same as the Default value.
Click the OK button and we can see that the parameter Database has been generated, which is shown in the Left Pane and also in the Center Pane.
Summary
I hope you understood how you can create a parameter. In the next article, I will show you how to use this parameter to import the database table, so stay with me and wait for the next article. Thanks for reading.
Join the conversation! Your thoughts help the community grow.