Parameters are a very nice example of getting more dynamic into your Power Query for building your Power BI datasets. Until recently, the configuration of parameters published to powerbi.com was a “little” bit annoying. To change a parameter value, you had to change the value in Power BI Desktop and re-publish the dataset. And again – the Power BI team released a small but great feature to simplify this process.
Let’s introduce: Manage your dataset parameter values directly in powerbi.com
In the following example I would like to guide you through a small sample exemplifying this new feature. I start with my data table – sales information that happened in December 2017 and January 2018. The data is stored in an Azure SQL Database.
The data modeling steps are very simple. Connect to the Azure SQL Database and select the source table.
Edit your data preparation steps by selecting “Edit” to open the Power Query Editor (by the way – this name was changed in a recent Power BI Desktop Update.. 😊)
In the next step, a parameter named paramStartDate is created and used in a subsequent step to filter the entries.
The filtering step is added by using a Date Filter on the SalesDate column and choosing the After.. option.
Define the filter options as shown in the following screenshot.
That’s it for data preparation. Please close the Power Query editor and start building a sample report.
Sample report (paramStartDate set to 1.1.2017)
Let’s change the parameter value (Edit Query – Edit Parameters) and hit “Apply changes” (plus Refresh in the report pane). Et voila – only sales info in 2018 is shown.
Publish the data set to powerbi.com and test the parameter configuration
As a next step, the created dataset (plus sample report page) is published to a powerbi.com workspace. The report contains the filtered data as shown above. In order to change the parameter value in powerbi.com you’ll have to open the dataset settings. There are two ways to open these – either by getting there through the settings option in the main menu or by using the “Schedule refresh” option.
Enter the new value for the parameter and hit apply. What you need to do next is to refresh your dataset (i.e. the parameter filtering is applied in this step).
In the case that your report was already open before you started the refresh, you’ll need to manually hit refresh in your report to display the new data.