Power BI is becoming very popular as a Business Intelligence solution. From a single user who wants to analyze some local data up to large enterprises who want to dive into huge volumes of data: Power BI is the tool!
But as the tool gets more popular, there are also more people bumping into limitations: Sources which are not supported, missing data transformations, limited machine learning possibilities, ... .
Another issue is that data science teams are often used to different languages than the DAX and M used in Power BI.
R (and Python) to the rescue
To overcome these problems Microsoft added support for two popular data science languages to Power BI: R and Python. R is already supported in Power BI Desktop as well as the Power BI Service since a few years. Python is more recent and support in the Power BI service was added just a few months ago.
Both languages can be used to extend Power BI in 3 ways:
- Source (which is demonstrated below)
- Transformation
- Visualization
Using R in Power BI
Before we can use R, we must first download and install the R framework. Both the CRAN as well as Microsoft R are supported.
Next we need to tell Power BI Desktop which installed version of R we wish to use. Configure this in the Power BI Desktop Options:
For the R source we just select it from the list of supported sources and paste an R script. For instance the script below produces a few rows of random data:
When writing these scripts we have to keep a few things in mind:
- Only R dataframe objects are available as tables in Power BI, so if needed use the
data.frame function
- Be aware that the R code is potentially executed more than once, better avoid side-effects in your R code
- There is no syntax coloring nor completion in the R editor, so better use an IDE such as RStudio or R Tools for Visual Studio to develop the R code, then copy it into Power BI Desktop
- If you plan on running a R source or transformation in the Power BI service, then the R code needs to run on-premisses via the gateway. For visualizations this is not needed: They run on an R installation in the cloud