May 2021 - Dibyendu Deb

In this article, I am going to discuss how we can use the analytical and visualization power of the R programming language within the Power BI. We can execute R script in Power BI to create data models, prepare reports, data cleaning, advanced data shaping and analytics, missing data computation, clustering, forecasting and many other advanced tasks.

Here are some articles in this series you may be interested in

R is arguably the most preferred language by Data Scientists. It is an open-source language backed by a vast community of developers and users. It has very rich libraries to perform almost all kinds of complex analysis.

The Microsoft Power BI provides a nifty feature of integrating the power of the R language. We can import R script within Power BI and thus perform even more complex analysis.

Installing R

To activate the R script into Power BI, you need R to be installed in the same computer you are using Power BI. You can install the R package from the CRAN distribution of the R project or go for Microsoft R open distribution(MRAN).

It will be good if you install a suitable R IDE too, like R studio. The free version of R studio can serve our purpose. The IDE helps us to check the R code if it has any errors. Because correcting errors of R code within Power BI can be difficult.

If you have already installed R and R studio you may have more than one version on your computer. Check the correct option of R while using it in File -> Options and settings -> R scripting.

Checking the R version installed in your computer

Importing data using R script

We can import data using the R script too. The “Get data” option in the Power BI helps us to import data. Here is an example of importing data I have saved in CSV format on my computer.

See the below image consisting of screenshots of all the steps from my computer. The “Other” option under “Get data” has the option to put your R script to import the data.

See the 4th step in the below image where a window gets opened and I put the following R script:

dataset <- read.csv(file="E:/test.csv", header=TRUE, sep=",")

The code is very simple, which imports a .csv file from my computer’s E:/ drive with its original header and using “,” as the separator.

As the given CSV file has only one dataset so the next window showed the dataset with the variable name “dataset” as mentioned by me in the script.

The R script should return at least one data frame. The Power BI creates tables from each of the data frames. If the data frame has columns containing complex or vector values, in Power BI they will display error. Any field with “N/A” will display as “NULL”.

If the R script takes more than 30 minutes to execute then it will time out. If there is any interactive field in the R script, like user input, then it may halt the script’s execution. In case your R script contains any file location, define the full path instead of providing a relative path.

Execute R script in Power BI

Here I will demonstrate executing a simple R script. My purpose is just to show the process how we can run R script inside the Power BI. So, the script will be very simple here.

The data consists of two variable X and Y. The Y is dependent on the X variable. I will run the “nuralnet" library of R to create a prediction of Y using X.

I have written the code in the external R IDE i.e. R studio and executed it there before using it in Power BI to confirm if it is free from any error.

In the Visualizations pane of the Power BI, you can see the icon for executing the R script. We need to click this icon and provide the variables we are going to use in the R script.

Unless the R script gets executed, the report view will show a blank R script visualization window. Copy the code from R IDE i.e. R studio, paste in the R script editor and run the code.

Error handling

If we have any error in our R code, the Power BI will throw some error. For example here I intentionally gave wrong dataset name. And Power BI has clearly mentioned that error in its report view. See the below image.

The error says that the object ‘a’ not found. This is because the object here is the dataset variable named ‘dataset’.

Once I have corrected the mistake, the script gets executed and here is the output. The neuralnet is applied and the corresponding neuralnet architecture is created.

So here is a quick overview of how we can use the power of R programming language within the Power BI desktop. R is the most preffered data science language and backed by vast community of data scientists and analysts.

I tried to provide a comprehensive guide on how to execute R script in Power BI with all relevant screenshots while doing it myself on my computer. And hope that you may find it helpful while doing the same for the first time.

So, try the steps on your own following the steps as described here. Please comment below if you have any queries or suggestions.