Smarter ideas worth writing about.

R Integration with Power BI

In recent years, the powerful visualization and forecasting capabilities of R have helped it emerge as one of the leading tools in the BI and analytics space. This popularity has led to many BI tools, such as Power BI, adding R integration in order to unlock the full capability of the software. Currently, there are three different ways to integrate R into your Power BI reports: the R Script data source, the Run R Script data cleansing tool, and the R script visual. In this blog we are going to explore all three so that you can begin using R in your Power BI reports.

R Script Data Source

The first way to integrate R into your Power BI report is with the R script data source. The R script data source allows you to load data from any data source, even those that may not be supported by Power BI but are supported by R. With the R script data source you can do much more than simply load data to your model. You can also use it to blend multiple data sources and create custom columns based off of R script calculations. To import data using R, simply write your R script in the Execute R Script window and run it. As a best practice, it is always good to test your R script in a program such as RStudio first to be sure that it runs without any errors. Once you have tested your script you can enter it into the Execute R Script window and load your data to Power BI.

R Script for Data Cleansing

A second way to integrate R into your Power BI reports is with the data cleansing tool. The July 2016 Power BI update included a new feature that allows us to add R steps in the Query Editor to enhance our data. This new feature gives us the ability to reshape the data, perform powerful statistical calculations available in R, and add new columns to our data set. Additionally, we can use R to score the data for use in predictive models. You can find the new Run R Script feature by opening Query Editor, navigating to the Transform tab and clicking Run R Script on the far right of the ribbon.

Once selected, a Run R Script box will appear with the comment ‘# 'dataset' holds the input data for this script’. What this means is that all of the data from your previous step is now contained in the new data frame called ‘dataset’ from which columns in that data frame can be accessed using the ‘dataset$ColumnName’ syntax. Using this data frame, you can write an R script to cleanse and perform calculations on the data.

R Script Visual

The final way to incorporate R into your Power BI reports is with the use of R script visuals. R script visuals are a great way to unlock visualizations and predictive capabilities that aren’t currently offered in Power BI. R visuals can be created off of any data source and can be added to your report with as little as one line of script. Once an R script visual is added to your report canvas an R script editor will appear at the bottom of the canvas. As you add dimensions and measures to the visual you will notice some auto-generated script in that R script editor.

Just like the Run R Script data cleansing step, a data frame called ‘dataset’ is created for you with duplicate rows removed. Your visual needs to be created off of this data frame. Once your script is ready, click the run button  in the top right corner of the R script editor pane to refresh your visual.

Power BI has made it easier than ever for developers to unleash the capilities of R in their reports. R allows us to load our own custom script as a data source, perform complex calculations and data cleansing on the backend, and use new and exciting visualizations that are not currently available to us in Power BI. R has really opened the box for Power BI users in terms of how they can manipulate and view the data in their reports. The capabilities are there for us to get more out of our data than ever before but it’s up to us to take advantage of these features.


About The Author

BI Developer

Kurt is a Senior Business Intelligence Developer with a strong background in analytical reporting and dashboard development. Kurt enjoys working closely with the business in order to help them see their data in ways that enable valuable decision making.