Overview
Back in the first post in this series, I mentioned that you can easily change how far back you can look to get information in your queries. This post will talk about PowerBI parameters that we will use to do this.
Create a parameter
Parameters are very easy to create. In left hand navigation menu, select the “Model” icon (the one at the bottom that looks like 3 tables with lines between them) to go into the modeling section.
From there, right click on any table and then choose “Edit query”. This will open the Power Query Editor page. In the header, in the “Parameters” section, select “Manage Parameters” and in the drop down select “New Parameter”
This will open “Manage Parameters” window where you can enter one or more parameters. We are going to create a new one for the number of days to look for information in our query. This is going to be a required text and I will use 7 days as the default. Fill in the values as shown below and click on the “OK” button to create the parameter. Note that if you want to create multiple parameters at one time you can click on the “New” link to add more parameters before you click on the “OK” button
A couple of things worth mentioning here. The first is that even though the number of days is actually a number, we have it set as a string. This is to make the query easier. If you want to use a number, that is fine, you will just need to do a conversion from a number to a string in the query.
The second is the “Suggested Values” drop down. I have this set to allow any value, but you could set it to “List of values” and enter the same values that you see in Microsoft Sentinel (but make only for the days values). You can also get the values from another query but that is beyond the scope of this blog post (meaning I have not done that, so I don’t know how to use it)
Now, the Power Query Editor screen should look similar to the image below. If you created more than one parameter, each one will be shown here, and you can click on the name to look at each one. You can change the value of this parameter here as well as click on the “Manage Parameter” button to edit existing or add new parameters.
Using a parameter
Now that we have the parameter we need, let’s see how to use it. Select the query you want to modify. I will use the “SecurityIncidents” one for this example. In the header select the “Advanced Editor” button in the “Query” section. This will open the Advanced editor window for the selected query.
In the query, look for the “timespan” entry. In the image below, I have it highlighted. Note that I have broken up the line to make it easily fit into the screen. You may need to scroll to find this entry.
If you are familiar with how the timespan is configured, you will know that this is saying to look 90 days in the past for the data. We are going to replace the “90” with our “DaysToQuery” parameter.
Select the “90” in the query and remove it. Now, we will be adding what is considered text to this query, so we first need close the string on the left side with a double quote so type that in along with a space. Move your cursor to the right so it is in front of the “D” and enter another double quote. This will ensure that we have the two separate strings.
Now we need to add the parameter name. Since this is a string, we will need to concatenate the strings together and this is done with the “&” character. Move your cursor to be just before the double quote you just added and enter “& “. That is the “&” character followed by a space.
Now if you start typing in “DaysToQuery”, you will see that a drop-down list appears with the matching entries. Select the “DaysToQuery” to add it to the string and then enter another space, a “&” character, and another space to finish the string. When you are done it should look like
This will now use the “DaysToQuery” value to determine how far back to look. Click the “Done” button to save your changes. Go ahead and click on the “Close & Apply” button as well to update all the data you have.
If you click on the “Record” icon in the left-hand navigation button, you will see that your data is only from the last 7 days now. If you click on the “Transform data” button in the “Queries” section of the header and select “Edit parameters” you can easily change how far back to look. You will need to apply the changes once you do, and PowerBI will tell you that you need to do so.
Summary
This blog post has shown you how easy it is to add a PowerBI parameter to change how far back you can look to get data. I am sure you realize how you could use parameters in other parts of your query as well.
This is the last blog post in the series regarding PowerBI that I am planning on writing, although that is subject to change if I find other cool things to do with PowerBI!
Some of the other actions you can take in PowerBI to make your reports really stand out include tabs to show different reports on the same page (hint, it involves using PowerBI bookmarks), custom tooltips, unlinking visualizations so that changing one visualization will not change the others, and drill downs to get more detailed information on a visualization.