As its the first post of 2019, happy new year to everyone and I wish you all the best for the coming year. With the new year comes the introduction of a new software for the blog in Microsofts PowerBI. This a powerful analysis and visualisation tool that in particular allows for the creation of dashboard style reports (See here for samples). However for our introduction we are going to look at how PowerBI allows for the integration of R scripts for data analysis and plotting (Thanks to @JackWalsh_93 for the suggestion).
Benefits of R in PowerBI
Some of the benefits of using R within PowerBI are:
- Allows use of R scripts in place of DAX based formulas
- Statistical analysis carried can be carried out and visualised in PowerBI with R scripts
- RStudio can be used as a development environment to facilitate R scripting within PowerBI
For now we will look at how to begin using R in PowerBI and some of the options available to integrate R scripts.
R Integration
First of all, the laptop/computer that you will be using PowerBI on must have R installed on it (See here). As we will see later, PowerBI also allows for the integration of RStudio so I would suggest having this installed as well(See here). PowerBI (Here for installing PowerBI Desktop) will usually automatically detect if these are installed on your machine if not you can manually tell PowerBI where to look by the following:
- Select File Menu
- Options and Settings
- Options
- R scripting
R Script to Load and Transform Data
We can use an R script to load and carry out data transformation or analysis in a single step through the following:
- Get Data
- More
- Other
- R Script
- Copy and Paste Script from R/RStudio
If there is already data loaded into PowerBI then we have more options available to us. We can use R to create visuals to transform our data and create visuals or we can carry out further data transformation.
For visuals:
- Select R from the Visualisations options
- Select required datafields from the Fields menu
- PowerBI automatically creates a data frame in the R environment based on the fields selected
- Next you have two options:
- Type R script into the box ending in a plot output
- OR Select the arrow in the top right corner of the script editor to open RStudio.
- Type R Script while in Rstudio then copy and paste script back into the PowerBI R Visual section ensuring the script ends in a plot (I have learned I’m pretty useless at R without RStudio by not picking this option ;)) .
- Select Run Script
For data transformation:
Open the Power Query Editor Window by:
- Edit Queries Menu
- Edit Queries
Within the Power Query Editor Window:
- Select the Transform Menu
- Run R Script (far right)
- Enter R Script
Notes:
- For those looking to run PowerBI through Parallels on Mac, R needs to be installed through Parallels as well along with any packages required.
- I would suggest running R with minimal package involvement
- When using R script through either the visuals or the Query editor options, PowerBI creates a dataframe called
datasetbased on your data - Data must be loaded through the dataframe file structure
- Packages must be loaded through the
library()function within PowerBI for them their functions to work. - R plots within PowerBI are limited to datasets of 150,000 rows, with larger datasets only the first 150,000 rows are used
- Scripts that take longer than 30 minutes or plots longer than 5 minutes are timed out within the PowerBI environment
- Full file paths must always be used when running r scripts in PowerBI not a shortened relative path (where the working directory has been specified)
- Although this hasn’t been an issue for me yet, some R packages will not work within PowerBI, particularly if they are still in development
That was an initial introduction to using R scripts within the PowerBI environment, in the near future I will cover using it in more detail as part of a series of posts where we look at how GoogleSheets/Forms, R, PowerBI and ShinyApps can be used to create a free Wellness questionnaire and largely automated analysis system.
For further reading on R in PowerBi here are some useful links
SportSciData 