PoweR BI

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!

Power BI popularity Google trends

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:

Configure which R framework to use in the 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:

Producing random data in Power BI using R

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