R is a popular language for data science, although recently Python is winning the language wars. Still, R has its proponents (yours truly) and will continue in its use throughout the data science community. Did you know that it can be used with Power BI, too? This quick tutorial gives instructions on loading R scripts into Power BI.
Prerequisites
This tutorial assumes you have a familiarity with both R programming and Power BI. You do not need to be an expert in either. This includes knowing how to load data in Power BI. If you are unfamiliar with these concepts, refer to the following websites:
DataCamp Introduction to R - the first chapter of this tutorial is free and gives you what you need to understand the example in this tutorial.
Purchases made from links on this page may result in the site owner receiving a commission.
Why Load R Scripts Into Power BI
There are a few reasons why you may want to load R scripts into Power BI. The first is you have several scripts that use or create data tables. Power BI has robust report capabilities and a powerful DAX language that can manipulate your data. While this manipulation could also be done in R, it may require more work to accomplish the same tasks.
Another reason is you may have access to R scripts that others wrote and they fit the bill for the type of reports you are looking to create. There are numerous and useful scripts available online. Why reinvent the wheel?
There could be reasons other than the above. However, these are two major examples.
Defining the Data Frame
R has a connect for R Scripts. These scripts are translated into rows and columns simular to a relational database table or Excel spreadsheet.
To keep this tutorial simple, we'll create a data frame with three rows and three columns. We'll call it our SalesTable with the date, product, and sales as the column names.
--------------- Copy Script --------------------------
date <- as.Date(c("2020-01-02", "2020-02-17", "2020-03-14"))
product <- c("X100", "X250", "Y332")
sales <- c(1050, 2500, 7235)
SalesTable <- data.frame(date, product, sales)
------------------ End of Copy Script -----------------------
Be sure to try the script out in R before trying to load it into Power BI. It's easier to debug in R if something isn't loading right.
Loading the Script
Once you have the script working (in R), start a new Power BI project and choose the R Script connector. To do this, type in the phrase "R Script" into the search box:
Choose the option for R script and click the <Connect> button.
At this point, Power BI may ask you to enable the scripting options. Simply select the Enable button to do so.
The next step is to copy the script above into the textarea and hit <OK>. When finished, you'll have a new table that can be used in Power BI just like any other table.
At this point, the data table is loaded just like any other table source. From here, you can either hit the <Load> or <Transform Data> button.
What About the Python Connector?
You may have noticed when searching for connectors, that a Python connector was available. I have not tried out this feature yet, but my guess is it will work in a similar fashion to R scripts. It's on my calendar to test out this feature and create a short tutorial. However, if you feel confident in trying it out for yourself, it should be as straightforward.
Get Started with R
Did you know you can get a free chapter of one of the most effective training programs on the internet? When you click on the link below, you will have access to the first chapter of an introduction to R.
What are you waiting for? Click on the link Now!
