Good day everyone and welcome to the latest Power BI adventure, this time featuring the mystical world of Power BI Desktop Query Parameters and Templates.
Today we will firstly look at what a parameter is, why it is used, how it is used and finally how we would go about creating these marvelous things called Power BI templates…….yeah, oh yeah (in the voice of Batman).
What is a Parameter?
A parameter (from the Ancient Greek παρά, para: “beside”, “subsidiary”; and μέτρον, metron: “measure”), generally, is any characteristic that can help in defining or classifying a particular system (meaning an event, project, object, situation, etc.). That is, a parameter is an element of a system that is useful, or critical, when identifying the system, or when evaluating its performance, status, condition, etc. “there are three parameters by which a speaker is able to modify the meaning of the utterance—pitch, volume, and tempo”. In computer programming it is a special kind of variable, used in a subroutine to refer to one of the pieces of data provided as input to the subroutine.[a] These pieces of data are called arguments. An ordered list of parameters is usually included in the definition of a subroutine, so that, each time the subroutine is called, its arguments for that call can be assigned to the corresponding parameters.
Synonyms: framework, variable, limit, boundary, limiting factor, limitation, restriction, specification, criterion, guideline; technical: constant “they set the parameters of the debate”
If we look at this descriptions above, we can identify a few key characteristics of a parameter: It is measurable, it’s an input, it helps define / classify a particular system, and is part of the system it defines. I know maybe a bit vague or confusing, but for now let’s just say that a parameter provides input to a function which generates an output.
Why are Parameters used?
Parameters give users the ability to use predefined inputs as query filters, data sources, measure definitions, and much much more…). Depending on the reason and use of the parameters a user can have one or more predefined parameter values, which give the user the flexibility to do a wide variety of things with their Power BI development. On top of this capability, templates allow users to export the definition of a report (reports, data model, queries, and parameters) without including the actual data. A user can then easily with the use of the template (.pbit) choose the required parameters, and the data, reports and model will load based on the parameter selection. Once loaded, the file can be saved as a .pbix file and published to Powerbi.com for sharing with other users in your organisation. It becomes particularly useful in cases where organisations use the same report model, structure, and queries, but need a user to change the content of the report by selecting the relevant query parameters. Generally speaking parameters can be accessed via Data Source, Filter Rows, Keep Rows, Remove Rows, Replace Rows, etc.
Ready for some parameter lunch? Let’s see how this works in practice shall we……
How Parameters are used?
To help us with the parameter exercise, we’ll be using the Data Bear Sales Analysis Demonstration. What we want to do is to create a template for an organisation where they would like to be able to use the same reports, model and structure, but want the ability to change where the data is sourced from, which client it’s used for and dynamically change the client site, data path and client name details with the use of Parameters.
SharePoint data source as Parameter
With Power BI Desktop up and running, from the ‘Home’ tab / ‘External data’ group, click on ‘Edit Queries’ to enter the Query Editor.
With the Query Editor open we can see all available queries, which currently sources my sales data locally from a PC file directory (here is the file path = “C:\Users\UserName\Sales\Sales Data.xlsx”. To automate the sourcing of the sales data, let’s use a parameter and source the data from a SharePoint file directory where all sales are loaded every month. For this exercise we will assume that the file names for all clients are called ‘Sales Data.xlsx’.
With the new Query Parameters feature, users can now easily define one or multiple parameters to be used in their queries, Data Model and report layers in Power BI Desktop. Users can define new parameters by using the “Manage Parameters” dialog in the Query Editor window.
From this dialog, users can create new parameters and specify metadata and settings for each parameter:
Name: Name describing the parameter (You can choose your own)
Description: This will be displayed next to the parameter name when saving and using as a template. It will assist the person who is using the template in specifying the parameter value and to better understand the purpose and use of this parameter.
Required: Users can specify whether a certain parameter is optional or a value for that parameter must be specified (required).
Type: This field applies a Data Type restriction on the input value for the parameter. For instance, users can define a parameter of type Decimal Number, Date/Time, Date, Time, Date/Time/Timezone, Duration, Text, True/False or Binary.
Suggested Values: In addition to Data Type restrictions, users can apply further restrictions to the allowed values for a given parameter. For instance, users could specify that the Data Type for a parameter is Text and restrict the acceptable values to a static list of Text values. Users will then be able to pick one of these values when specifying the parameter value to use. This enables users to have dynamic sets of options when setting up their parameters. A good example for this would be making a “Sales Person” parameter change the list of values to select from, based on another parameter that allows users to specify a “Department” or “Sales Categories.
Current Value: This setting allows users to specify the value for this parameter in the current report.
Follow these steps to create your first parameter:
- Click on ‘New’
- Type Parameter Name (Ours is ‘ClientSitePath’)
- Type description of Parameter (Be as descriptive here as possible)
- Select whether this Parameter should is Required or not (The site path should be a ‘Required’ field)
- Select the Parameter type from the drop-down list (The path we need to enter is a text field, so we’ll select ‘Text’)
- Select from the ‘Suggested Values’ drop-down list whether you want the user to enter ‘Any Value’, select from a ‘List of values’ or if the values should be dependent on a ‘Query’. In our case, we want the user to enter ‘Any Value’.
- Enter the ‘Current Value’ by typing the ‘ClientSitePath’ you want to use.
- Click on ‘OK’ to submit your new Parameter.
We have created our first Parameter which aims to a specific client SharePoint site, but still require more parameters to show us the client data folder and client name. Use the same steps as above and create these parameters as circled in blue above, one called ‘DataPath’ and one called ‘ClientName’.
So now we have three parameters:
With your parameters now created, let’s start by getting our query sorted out.
We will be using the following query script to get our data from SharePoint = SharePoint.Contents(ClientSitePath, [ApiVersion = 14])
Some info on this function:
SharePoint.Contents(url as text, optional **options **as nullable record) as table
url. Each row contains properties of the folder or file and a link to its content.
optionsmay be specified to control the following options:
ApiVersion: A number (14 or 15) or the text “Auto” that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.
Notice that instead of the Url as text showing the actual site path, I’ve entered our first parameter ‘ClientSitePath’ which points to the client site we’ve created earlier – https://databear.sharepoint.com/sites/BlogDemo/. The API version here is by default 14, but if you require this for Non-English SharePoint sites, then enter the API as .15
- From within the Query Editor, we’ll select the ‘Sales’ table and replace the existing query editor code with the following line: = SharePoint.Contents(ClientSitePath, [ApiVersion = 14]), press Enter. You’ll now see that our query has returned a list of the contents available from our ‘ClientSitePath’.
- We know from our site that our data is under the documents folder, let’s drill-down by clicking on the ‘Table’ Content.
After pressing enter in 1. above to apply your new query, Power BI will ask you for credentials for the user who wants to access the SharePoint site data.
- From the Microsoft account page, Click on ‘Sign-in’
- A pop-up box will appear and ask you to enter your SharePoint site credentials, in this case it is my Microsoft credentials. Enter the username and password and click ‘Sign-in’
- With the login successful, it will show that you are signed in.
- Click ‘Save’ to finish the login and save your credentials.
- Great!! Now let’s drill-down further by clicking on the ‘Table’ Content of the folder called ‘Sales Data’.
- Once again let’s drill-down further by clicking on the ‘Table’ Content of the folder called ‘Client 1’
- This is where our Sales Data file is located on our SharePoint site, let’s click on ‘Binary’ to open the excel file where our Sales Data resides.
- Now we’re at the lowest level where the Sales Data table is ready to be loaded from our excel file into the data model. Click on ‘Table’ to load the data into our data model.
- The data is in!! Now let’s change the first line of data into our header line, by clicking on the ‘Use First Row as Headers’ from the ‘Home’ tab, under the ‘Transform’ group.
- From the ‘Home’ tab, under the ‘Query’ group, click on ‘Advanced Editor’ to open the ‘Advanced Query Editing Dialog’ box.
Awesome, this is where the business happens. Let’s add our parameters into the query.
- From the original query, replace the Documents folder name to the ‘DataPath’ parameter.
- From the original query, replace the ‘Client 1’ name to the ‘ClientName’ parameter where our data resides.
- We’ve already replaced the SharePoint site name earlier by entering out ‘ClientSitePath’, so there is nothing left to do here.
Click ‘Done’ and ‘Close and Apply’ to apply your new query to the data mode. At this point it’s important to note that your new query now references your parameters for ‘ClientSitePath’, ‘DataPath’ and ‘ClientName’, so any changes to these parameters will effect the changes necessary.
Creating our first template
With the data loaded, you can now choose to go to QueryEditor, adjust any of the parameters, or use the report with it’s predefined parameters and save the file as a template. Let’s see how this is done…..
- With your report open, click on ‘File’ and ‘Export.
- Choose the ‘Power BI template’ as option to export your reports.
- Type a description for your template, we’ll call ours ‘Sales Demo Template’
- Click ‘Ok’ to apply.
- Browse to where you would like to save your template.
- Type a file name for your template, we’ll call ours ‘Data Bear Sales Template’.
- By default because we chose ‘Export’ as Power BI template, this option is already there for us.
- Click ‘Save’ to save your template to the chosen destination.
Cool!! Now, let’s go to the directory where we saved our file and open the template.
What is this? When we open the template for the first time, it asks us to fill-in the details for the parameters we identified in our file earlier. This I’m sure will make Batman, Spiderman or any other business superhero very, very happy.
Let’s fill in the details
- For ‘ClientSitePath’, lets enter our website address – https://databear.sharepoint.com/sites/BlogDemo
- Let’s enter our ‘DataPath’ – ‘Sales Data’
- Finally, because we have numerous clients – we want to be able to specify which client data we want to use – let’s choose ‘Client 1’
- Click ‘Load’ to see the magic in action…..
As soon as the ‘Load’ button is clicked, Power BI jumps into action like Batman on a hot tin roof – or is that a cat? Anyway, it loads all the data, reports, structure, and model based on the parameters we fed it for lunch – Yeah oh yeah!!!
A completed report, within seconds for a new client and data source – pure parameter joy!!!!
Parameters and a very exciting function – does make you feel like Batman doesn’t it, but without the suit.
Thanks so much for joining us again on this exciting Power BI journey, I hope you will find as much value in Parameters as a data SharePoint data source as what we have. Please remember to leave your comments below, or any questions that you may have. More info on this here.
Until next time, stay safe, and be blessed!!!