What are parameters in Power BI? 

Parameters are used in Power BI to store and manage values dynamically. Dynamic parameterization gives the flexibility to dynamically populate column/table values based on the requirements. It stores values that can be used in the Power Query Editor for transformations.  

Use case 1: Get a subset of data from a data source like Excel, Azure Synapse, etc., into a Power BI report 

To use these parameters in the Power BI Query Editor, we need to create them in the Power Query Editor Home –> Manage Parameters –> New Parameters. 

Here, for demonstration purposes, we have created two parameters: StartDate and EndDate. These created parameters can be used as date ranges to pull only the required set of data into the Power BI report dataset. 

 

Below are the parameter properties that give metadata of a parameter created: 

1. Name: Give a unique name to your parameter that you want to differentiate and recognize the parameter wherever you use it in the Power Query Editor. 
2. Description: This helps the user understand the purpose of creating this parameter. It will be displayed as a tooltip or summary whenever parameter editing is done. 
3. Required: This gives the choice of whether providing a value for the parameter is mandatory or not. 
4. Type: The parameter’s datatype can be selected from the Type dropdown list. Based on this datatype, the current value field will be holding a placeholder value. 
5. Suggested value: It gives the user flexibility to select any value, list of values, or query based on the requirements.  
6. Current value: Based on the suggested value selection, the current value needs to be populated. This value acts as a default value of the parameter created. 

Click on OK to create the parameter. 

On creating our required parameters, go to the “Advanced Editor” of a particular table whose parameterization needs to be performed and paste the following as a query. 

 

Query = “Select * FROM Employee where Joining_Date between “& StartDate &” AND “& EndDate &”” 

Here, we are pulling a subset of data from the Employee table, which falls in the date range, that is set in parameters: StartDate and EndDate. 

Then click on  OK to save the changes and Close & Apply to exit the Power Query Editor. 

As you land on the Power BI Desktop, now the data will be loaded according to the “Current value” of the date ranges. You can review and edit these parameters anytime and get the latest data from your data source based on the parameter values.  

Use case 2: Pass a value to dynamically populate the calculated column’s value in Power BI  

The calculated column in Power BI is a logical extension of a table calculated for each row. By using dynamic parameterization to populate a calculated column by any mathematical operation (addition, multiplication, etc.), we can have an entire column dynamically created with different values taken from the user.   

To use these parameters in the Power BI Query Editor, we need to create them in Power Query Editor Home –> Manage Parameters –> New Parameters.

 

Here, for demonstration purposes, we have created a parameter called “Multiplicative Factor.” This value will be taken from the user, and an entire column will be multiplied with this factor while it comes from a data source like Azure Synapse. You can give its “Data Type” and “Current Value” on its creation as requirements and click on OK.  

 

These dynamic parameters can be reviewed and edited in both Power BI Desktop as well as Power BI Workspace as per the requirement. As a report author, you can define parameters in Power BI Desktop. When you publish that report to the Power BI Service, the parameter settings, and its selections travel with it. 

How to edit and review parameters in Power BI Desktop 

In Power BI Desktop, go to  Home and expand  Transform data  to select  Edit parameters. 

 

Select a particular parameter to review or update it as required. Then click on OK to save the changes. 

 

How to edit and review parameters in Power BI Workspace 

After you have published the report successfully from the Power BI desktop in your desired workspace, go to the Power BI services, and move to  Datasets + Dataflow  to view the list of published reports.
Select more options and go to  Settings. 

 

In the  Datasets option, expand the parameters tab to see all the parameters you need to review or edit. 

 

Click on  Apply after required updates to save the changes.

Likewise, there are many such use cases where a parameter can be used as an argument to perform multiple transformations or to create new query functions or conditional column values generation.

References 

• Microsoft: Use Power Query parameters
• Microsoft Power BI: Service parameters

• How to Use Power BI Parameters to Create Dynamic Power BI Reports

Keep learning 

Looking for more tutorials? Check out my previous tutorial on data storage at the edge using Azure Blob Storage and IoT Hub.  

You can also find more tutorials from the Neal team here.