R is a Powerful Tool That Can Add Immense Value to PowerBI; Where do I Start?

Chances are, that if you are interested in Business Intelligence then you’ve heard of R. It is a powerful and widely used tool in statistical analysis. But where do we start to learn and practice implementations in R? Well, we at FreshBI have the tools and knowledge to teach you the basics. We have put together a data set and training manual that can take you through some basic syntax and some examples of basic visuals in R.

In this blog post we will walk you through the steps that it takes to bring experimentation in R to life in PowerBI. The script used in this blog has is developed in detail in our training package (available here).

If this blog post is too basic for your needs then get the full course material here and work through it yourself or subscribe to FreshBI’s services to get coached in R and power BI.

Have a Look Here at This Step by Step Breakdown of Taking a Completed R Script in to PowerBI:

Prerequisites: R Script & Data.

The purpose of this step by step is not to build an R script, but rather to take a completed R script and port it into PowerBI. The data can be found here. Below is the script we will be using today.  

library(ggplot2)

model = lm(dataset$WAITING~dataset$FISCAL_YEAR)

slope = summary.lm(model)$coefficients[2,1]

int = summary.lm(model)$coefficients[1,1]

predict = slope*2020+int

max = max(dataset$WAITING,na.rm = TRUE)

label = paste("2020 Predicted Average Waiting is", round(predict,digits = 2))

gg = ggplot(dataset, aes(x=FISCAL_YEAR,y=WAITING))

gg = gg + geom_jitter(aes(color = dataset$HEALTH_AUTHORITY))

gg = gg + geom_smooth(aes(color = dataset$HEALTH_AUTHORITY))

gg = gg + geom_abline(slope = slope, intercept = int, colour = "black")

gg = gg + geom_text(x=2011,y=max*0.95,label= label,size = 5)

gg

Prefer to be coached through this process ?

FreshBI offers this training in-person or download the training material below at no cost, both covering the 4 phases below:

1. Basic syntax in R.

2. Using these basics to create some basic visuals in R.

3. Using a package to create an advanced visual and a script.

4. Using what we learned in 3. to create a script for PowerBI.

The follow-along manual, the data we used, and our finished PBIX can be found here.

1) First we Need Data:

We are going to load our data into PowerBI just as normal. With our data source Surgeries.csv downloaded, click get data and choose type Text/CSV. Choose our source and click load.

2) Next We Need to Create an R Visual:

Now that we have data we need to create a visual. Click the R visual icon in the Visualizations pane. Next we are going to need three fields: Drag FISCL_YEAR, WAITING, and HEALTH_AUTHORITY into the Values section.

2-b) Set All Values to ‘Don’t Summarize’:

I can’t tell you how many times I’ve gotten errors in my R scripts in PowerBI because I’ve forgotten this step. This is a critical step. It deserves its own step, because in most cases it breaks your script, and it is not an obvious fix if you’ve missed it.

3) Insert Our R Script:

Select the R visual and paste the script in Prerequisites) into the R script editor. Once you’ve done this you simply got to click to run button in the top right of the script editor.

You are all set!

The point in this is that pushing scripts into PowerBI is fast and simple. The only thing stopping you now is getting a grasp of basic R. Remember to have a look at our training package to started!

Remember, you can always feel free to contact me with questions, or to schedule training sessions.

Our Latest Blogs

About FreshBI

Based in USA and Canada, we have helped hundreds of businesses achieve excellence & success through business intelligence.

Power BI runs in our blood, and we are ready to take your business to next level.