Using R Inside of Microsoft’s Power BI

By | 2017-09-05T13:42:14+00:00 September 5th, 2017|Advanced Analytics, Data Science, R, Reporting|0 Comments

Machine learning can enhance your reports so end users can gain deeper and more valuable insights from the data. While the value can be tremendous, implementing automated machine learning for reports can be challenging so many organizations choose to run scripts manually before importing the data into a reporting tool.  Power BI makes it easier to implement automated machine learning for reports by allowing developers to copy and paste their machine learning R scripts, well really any R Script (it does not have to be predictive), into a window. This inserts a transformation step into the desktop application. Power BI is perfect for hosting small to medium sized machine learning scripts on reporting data.

In this post, I will demonstrate the usability of predictive analytics within a reporting tool. This is a very basic R script that uses the mtcars dataset. While it is not a complex script and does not model a large dataset, it is a great example of how to easily insert machine learning into your reports.  

First off, we need to get data inside a Power BI Desktop file. By clicking on “Get Data” on the upper left we can import the mtcars CSV.

Now it is time to write our R Script! In this case we want to predict a car’s miles per gallon (mpg) by using the number of cylinders (cyl), the car’s weight (wt), and the car’s horsepower (hp). Our script will use a linear regression model.

To implement this script, we will need to go into the “Edit Queries” tab located on the ribbon. Once in the edit queries view we hit the “Run R Script” in the “Transform” tab.

In the above code, we have our input dataset called “dataset”. This dataframe is by default the result from the previous applied step on the right of the query editor view. We then perform the following steps:

  1. Set the dataframe df equal to our input dataset.
  2. Create a model using the lm function.
  3. Apply the model to our output dataframe, df.

After we click “OK” we see the view below. To view the results of our R script we need to click on the “Table” hyperlink to expand the columns.

Now we can see the entire mtcars dataset with our predicted MPG for each car.

Now that we have applied an R Script to our dataset we can begin creating visuals for our end users!

Power BI is a great place to run R scripts for transformations and machine learning. Incorporating predictive analytics within a Power BI Report is a great way to bring more value to your organization. If you have any questions please contact

About the Author:

Leave A Comment