Power BI has integration for both R and Python. This integration can be used within M to utilise the ETL properties for both of these languages or to utilise the visual elements of R in a container. Today we are going to look at the latter.
As of the time of writing this to use R within the Power BI Service the user must have a Power BI Pro License.
I am going to go through how to use the R language within Power BI Desktop, and for this reason I will keep the scripting straight forward. In order to concentrate more on the end to end process.
R Studio Preparation
For next part I am going to open R studio and create a simple script:
library(forecast)
# group searches by date
dataset <- read.csv("data_agg.csv")
# create a time series object based on days of year as our data is on a daily basis
ts <- ts(dataset$Arrivals, frequency=365)
# pull out the seasonal and trend from the time series (train the forecast model)
decom <- stl(ts, s.window = "periodic")
# predict the next 365 days of searches
pred <- forecast(decom, h = 365)
# plot the predicted searches
plot(pred)
You’ll see I am bringing in a .csv file for the moment to test our script. This same file I will bring into Power BI through Power Query later.
This file contains some dummy A & E attendance data by date and ordered correctly. After we have loaded in the data, we then load it into a time series object, declaring the frequency to 365 as we want to pull out those yearly trends within our data. The next step is to then pull out those seasonal trends.
Finally, we use the forecast function to predict 365 points into the future and plot. You will see a plot appears with a future trend:
Power BI Preparation
To work with R in Power BI Desktop, you need to first tell it where R is installed. Open Power BI and go to File > Options and Settings > Options > R scripting:
If you have R installed prior to installing Power BI these settings should be automatically set for you. If they aren’t set navigate to your R folder and set the R home and the IDE (in my case R Studio).
Next, I am going to go to Get Data > Text/CSV and select my csv file data_agg.csv. Once loaded in I am going to do nothing more than remove the first column within the data, as this is just an index column. I then press close and apply to load in the data.
Creating visuals in Power BI
Next step is to then bring in an R visual:
Once the R visual has been dropped onto the canvas. You will see an empty container and a script editor at the bottom. Now drag Arrival Date and Arrivals into the container:
You will see that it has created some code for us already. Power BI very helpfully creates a data frame from the columns that we have dropped in, it also then make sure the data being dropped in is unique.
The code we created at the top can now be copied into our script editor with a few amendments. We just need to comment out the read.csv line as our data is now coming through via Power BI:
Once pasted click the run button in the script editor bar above.
You will see the same plot from before appearing within the container.
Interacting with the R visual in Power BI
Now it is in Power BI we have many options at our disposal. For example, we can add a date slicer. Select a slicer and drop ArrivalDate into it. You will notice that when we make any changes to the slicer the visual will also refresh:
Hopefully this has given you a simple overview of how you can bring in the power of R into your Power BI applications.
There are many other applications of this. Any interesting uses please ping them across to us and we will populate on our blog.