Microsoft Power BI supports adding parameters for queries and use them with various areas. However, since it has some limitations and cannot use with all areas, it has not been noticed much. Let's talk about Power BI parameters and see the way of using it.
Let's take a scenario and see how parameters can help on that. I have a set of CSV files that shows monthly sales. We need to to create a report using one of the files and then publish. Whenever the report needs to be refreshed for a different file, I need to change the data source by changing many things. What if I can just open the report using Power BI Desktop and change the source using a menu and get the report refreshed rather changing many things?
It can be done with Power BI Parameters. We can create a parameter with predefined values (in this case, source file names along with paths) and use it for changing the source.
Power BI Parameter can be created in three ways;
- Parameter that accepts a value via an input box
- Parameter that is formed using a predefined list
- Parameter that is formed using another query
Parameter with a predefined list
Let's focus on the second one first. Here are the steps for creating a Power BI report with a parameter;
1. Open Power BI and connect with a CSV file. In my case, the file name is Sales_201501.csv.
2. Once the file is loaded, click Edit Queries in Home ribbon for opening Query Editor. You should one query under Queries.
Parameters can be created only with Query Editor. However, created parameters can be accessed in both Query Editor and Data Model.
3. Click Manage Parameters in Home ribbon. Once the dialog box is opened, click New to create a new Parameter.
4. Name the parameter as SourceFile.
5. Select Text for Type.
6. Select List of values for Suggested values.
7. Enter File Names along with paths.
8. Set Default Value as you need. This is the default value for the designer.
9. Set Current Value as you need. This setting is for the end user, or to use with the report level.
Now you should see the parameter in Queries. If you want to open it as another table in the data model, you can right click on it and select Enable Load. We do not need to enable this option for using the parameter at report level.
Now we need to make sure that source of our file is set with the created parameter. There are two ways of doing it. One is open the Advanced Editor and change M Code. Other way is, using Data source settings. Let's use the second option. If you need to know how the M Code is getting changed, open it and see after the change.
10. Click Data source settings in the Home ribbon. This opens the Data Source Setting dialog box.
11. Select the source and click Change Source.... This opens a dialog box that allows you to change settings related to your source.
12. You should notice that the button given for File Path has an option for creating parameters and selecting a parameter.
13. Select Parameter as the option and select name parameter created.
14. Click OK and close the Data source settings.
15. All set. Click Close & Apply for saving the queries and closing Query Editor.
16. Create a visual as you can distinguish results when different file is selected. This is what I created from my file.
Let's change the source using the parameter now.
17. Click down-arrow in Edit Queries in Home ribbon. You should see three options. Select the last one that is Edit Parameter.
18. You should see Enter Parameters dialog box that shows all parameters. Change the source file to different one.
19. Once clicked OK, you should be prompted for Applying. Apply the settings and you should see the report with new source now.
Like this way, we can use the parameter in other sections like Filtering rows in Query Editor, Replacing, etc. We still cannot use the parameter with DAX and it is one limitations.
No comments:
Post a Comment