Power Bi is a great tool for data discovery with new functionality being made available all the time. However, this functionality is still limited when compared to a mature platform like R and you may come across scenarios where R is a better choice. It is for just such cases that Power BI now supports R script as a data source. Three scenarios come to mind where this might want to do this:

• An unsupported data source in Power BI.
• Complex transformations where R libraries are needed.
• An existing set of R scripts that would take too long to migrate.

Setup

Before you can use R scripts inside Power BI you need to ensure that R is installed on your machine and correctly configured. You can check the R script settings by going to   File -> Options and settings -> Options. Under the Detected R home directories option is a drop box listing the available R installations. If no version of R is available you can install R by following the How to install R link which leads you to the install files for both R Open or CRAN R.

Import

Once R has been configured within Power BI you being importing data as you would with any data source in Power BI by going to Get Data -> More -> R Script -> Connect

This will present you with the following dialogue.

There is very little functionality in this window for you to develop your scripts with so you will be better off building your code in your favourite IDE (R-studio or R-tools) and then simply pasting the tested code into the script window and press OK.

Power Bi will then execute the R script using configured installation of R, this may take a while if you are importing large amounts of data or loading data over the internet. Once the script has run a new dialogue box will appear containing a list of available results.

In my sample script, I have 4 different data structures 2 vectors, 1 matrix, 1 data.frame and 1 data.table, only the data.frame and data.table are available for selection and not the primitive data types as they do not have a similar data type in Power BI. You are then able to choose the result sets you want to import into the Power BI model. This is handy when you want to exclude any intermediate result sets. Once you have selected the results sets you wish to include you can import them into the model by selecting the Load option. The data set will be loaded and made available in the report tab for to use as you would any other data source, you are also able to create relationships between data imported via M (Power Query) and your new R data source.

When we use R script in Power BI, Power BI  does not replace its own internal formula engine with R but simply makes a call using M (Power BI’s build in formula language) to R using the R.Execute function. This can be seen by selecting the Edit button on the Navigator window (see figure 5) or by selecting Edit Queries – > (select you R script data set) -> Advanced editor. This brings up the Advanced Editor window where you can apply many of the available Power BI transformations.

Script files

While embedding the R script into the Power BI model makes it easy to develop and distribute, applying multiple changes to several Power BI documents that all use the same scripts can be tedious. To create more maintainable code is it may be better to use the source() function in R to execute the R script from the file system. In this way any changes made to the script it will be made to all Power BI documents using that R script.

Conclusion

While Power BI does a great job of importing data it still has a limited set of transformations and does not have nearly the variety of options available that R has.For this reason, you may need or want to use R as an alternate method of importing data into your Power BI model.