Power BI integration with R.

With Power BI Desktop, you can use R to visualize your data. Power BI Desktop does not include, deploy or install the R engine. To run R scripts in Power BI Desktop, you must separately install R on your local computer.

R installation settings are centrally located in the R Scripting section of the Options dialog. To specify your R installation settings, select File > Options and settings and then Options > R Scripting. If multiple installations of R are available, a drop-down menu appears that allows you to select which installation to use.

If R is installed is not identified, you can explicitly provide its location in the text box provided when you expand R Installation Settings.

There are two different ways to apply R in Power BI: the R Script for loading and transforming data, and the R Visual for additional enhancement and data visualization.

Create Power BI visuals using R

  1. To demonstrate the above we would be loading a sample csv file into the powerBI. Download the sample excel from the below url:
    https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip
  2. Using the above csv file we have loaded data into the powerBI. This excel is an information set of various currencies and their exchange rates. In the demo below we would be using R visual to demonstrate the correlation amongst the various currencies.
  1. Open R and have the following package installed on the R services.
    > install.packages(“corrplot”)
    This is a package/library which is used for correlation. There are a number of libraries which come as a part of R-services.
  1. Go to the PowerBI, click on R script on the visualizations. When we click we would see the R Script Editor at the bottom. This is the place where we would be writing the code

4.      Select the columns from the dataset where we would be performing a correlation. The ones we select would be seen as part of        dataframe as shown below.

5.      Now, have the following command on R Script Editor:

Copy to Clipboard

Here we are binding the fields which we want to use in the plot and finally using the correlation there. The output is the visual as shown below:

Now, we would be going ahead with how this can be integrated with PowerBI. We can have this visual talk/communicate with the powerBI. This is what their integration is about. We would put up a filter, a date filter and as per the date we select we would see this R-Visual change accordingly (Just like the way it does with other PowerBI visuals).

This ‘date’ column we have added a new column to the dataset, which is extracting ‘Year’ from the existing ‘Date’ column and putting it up as a new column ‘Year’.

We would use this computed column to act as a filter and make selection. See the screenshot below:

Run R scripts in Power BI Desktop

With just a few steps in Power BI Desktop we can run R scripts and create a data model, from which we can create reports, and share them on the Power BI service. R scripting in Power BI Desktop now supports number formats that contain decimals (.) and commas (,)

To run an R script in Power BI Desktop, create the script in your local R development environment, and make sure it runs successfully.

Now we would be running the r-script into PowerBI and load data. Below are the steps followed:

  1. In Power BI Desktop, the R Script data connector is found in Get Data. To run your R Script, select Get Data > More…, then select Other > R Script as shown in the following image.

2.  If R is installed on your local machine, the latest installed version is selected as your R engine. Simply copy your script into the           script window and select OK.

Below is the script I used:

Copy to Clipboard

When we click OK, the data would be loaded and we can see the data model with the connection established amongst the two datasets. From there on the datasets can be sued to generate the visuals. Please see the snapshots below

Reference Links:

https://www.youtube.com/watch?v=SMaJf6UBKeI
https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals
https://docs.microsoft.com/en-us/power-bi/desktop-r-scripts
https://www.blue-granite.com/tutorials/power-bi-and-r