Power BI Tutorial on Executing R Scripts in Power BI Desktop

Author by Jake Borzym

In this Power BI tutorial, I will show you step by step how to execute R script in Power BI desktop to create an awesome visualization of event attendees for our marketing event. First, we have to install Power BI desktop from Microsoft website for free. If you have already downloaded Power BI desktop, then please update it on Microsoft website by downloading the latest version. Once you download the latest version of Power BI desktop, you can follow along with me step by step to execute your first R script in Power BI desktop. The purpose of this demonstration is to show you the power of R to manipulate the data in R and leverage the interactive graphics of Power BI desktop. In this step by step tutorial, we are going to use Concurrency's event attendee data file. The file contains the information of attendee name, company, job titles etc. We wanted to visualize the job titles and companies visiting our event so that we can customize our event presentation as per the audience job titles and company sectors joining our event. Here's how I used R script and Power BI to create that visualization.

1) Launch Power BI desktop and click on GET DATA as shown in the image below.

step-11.jpg

 

2) Click on other as indicated by the arrow in the image below. Then select the R SCRIPT BETA option as shown by the arrow and click on CONNECT to launch the R script editor.

step-2.jpg

3) The R Script editor option is under the BETA version so Microsoft will give you a warning that the queries may not perform as per your expectations till they fix all the bugs.

step-3.jpg

4) I have written a script in R-studio. I have used famous Hadley Wicham package DPLYR to manipulate the data as per my requirement. I will go into details of the code sometime later as the purpose of this tutorial isn't explaining R Scripts. The final output of this script is two datasets - one of the top 10 companies and second dataset contains information of top 10 job titles interested in our Modern Developer event.

step-41.jpg

5) Then I copy the above R script from R-studio and paste it into the EXECUTE R SCRIPT tab presented by Power BI desktop after selecting PREVIEW CONNECTOR in step four. It is also important to expand the R INSTALLATION SETTINGS option to make sure that your R environment is configured properly. You can see that I'm on R 3.2.2 version.

step-5.jpg

6) When you click OK in step five, you should see the following screen as shown in the image below. R script generates 4 datasets. But, we don't want the raw datasets. So, we go ahead and select only top 10 companies and top 10 job titles dataset. That's the beauty of R that we can use the manipulated data instead of raw data to carry out our analysis and save time. Click LOAD and you will see 2 datasets in your Power BI work environment on the right hand side corner.

step-6.jpg

7) Now, all you have to do is select those 2 datasets and create two visualizations in Power BI desktop and select the type of visualization. I have created a pie chart of top 10 companies and a bar chart of top 10 job titles. I am not using any of the filter and values options provided by Power BI desktop as all my calculations and data manipulation are carried out in R. This is ver powerful as data can be cleansed in R and only final datasets can be imported in R to leverage interactive visualization power of Power BI desktop.

step-7.jpg

 

 

Author

Jake Borzym

Marketing Manager