In my last post, I described a problem I had working with an R script in Power BI for which the cause was not obvious (at least to me). In this post, I want to share with you what I was doing with R in the first place that led me to that particular problem.
Nothing particularly earth-shattering. As part of my latest Pluralsight course, I was preparing a demo to show how you can use R in Power BI. One way is to use an R script to get data into Power BI for analysis and another way is to use it to generate a visualization.
For my demo in the course, I used National Oceanic and Atmospheric Administration (NOAA) data to compare average afternoon temperatures by month for two different years and created a script to grab the necessary data by using the rnoaa package.
For the Pluralsight course, the R script uses hard-coded values for the location (which happens to be my home town, Las Vegas) and time period. If I want to view data for a different place or different years, I need to modify the R script.
It’s not difficult, but the cool thing about Power BI is that I can use parameters to dynamically change the report visualization without opening up the script. To do this:
- Open the Query Editor in Power BI
- Click Manage Parameters, and then click New Parameter.
- Set the parameter properties – Name, Type, and Current Value.The Name is how I will reference the parameter my R script, the Type is the data type, and Current Value is the initial value that I want to set (if any).
I set up 3 parameters as follows:
Here’s the Parameters dialog box showing all 3 parameters, with the details for Station.
And here’s the Years parameter, which includes a description:
Parameters can be used in many different ways in Power BI – to change a data source or change filter values or even replace values in a query based on parameter. You can also reference query parameters in DAX expressions and you can use query parameters in report filters. In this case, I’m going to plug the parameter values into the R script. The easiest way to do this is to open the Advanced Editor and replace the hard-coded values with parameter names.
Here’s the beginning of the script as it appears in the Advance Editor:
Source = R.Execute(“library(“”rnoaa””)#(lf)#(lf)station <- c(“”724846″”)#(lf)wban <- c(“”53123″”)#(lf)years <- c(“”2016″”, “”2017″”)…
Notice in the R.Execute function that the R script is just a big long string with new lines appearing as #(lf). To use the parameters, I simply need to concatenate the script and the parameters together, as shown below. (I’ve used bold font to show the changes I made.)
Source = R.Execute(“library(“”rnoaa””)#(lf)#(lf)station <- c(“”” & Station & ““”)#(lf)wban <- c(“”“& WBAN & ““”)#(lf)years <- c(“& Years & “)…
Now when I close the editor and view the report, everything works just as if I had hard-coded the values in the script. But now the fun part. To change the values, I click Edit Queries on the ribbon and then click Edit Parameters to open the dialog box shown below in which I can now type in new values. The parameter for which I added a description has a little symbol next to it that displays the description when I hover the cursor over the symbol.
This is all well and good, as long as I know the station and WBAN values that I want to use. I have a document I downloaded from the NOAA site to look them up, but I could set up other R scripts to get the full list of possible values from which I could choose. That will be another post!