Power Bi Vs R Using R in Power BI - Predictive Analytics using Power BI

With R code you can extend the functionalities of Power BI considerably. There are many questions on the web about how to do it, especially when building your own package with R visualization. To help answer some of them, I will go through the possible areas of the application of R script to Power BI. It will help you see your data more clearly and make better informed business decisions.

Power BI as a self-service BI tool has many limitations related to data sources, data processing or data visualization. However with the help of R, many of these restrictions disappear.

R allows you to create highly customizable visualizations, implement an advanced data processing logic, and also allows you to collect data from sources not supported by the standard, built-in Power BI connectors.

What’s more, it also allows for data processing implementations at the loading or post-processing stage. This is very convenient when our data requires programming, and the built-in Power BI functions are not capable of clearing it.

However, please note that many of the possibilities that open up to us in R are not transferable 1:1 to Power BI due to the limited number of packages which are supported by the tool.

Let’s go through the process of implementing R into Power BI, so that we can discuss its implications and benefits.

What are the main applications?

You can use R script with Power BI in several ways. The key ones include:

• Data source – expand the range of available data sources, provide data processing logic at the import stage
• Processing script – enrich data processing capabilities and engineering features
• Visualization – extend the range of visualizations available in R and implement charts
• As a part of Power BI visualization – gain the ability to build R-based visualizations and package them into ready-to-put boxes directly into Power BI.

How to implement R language in Power BI?

The first thing to do to be able to work in the R + Power BI configuration is to install R locally – preferably from the Microsoft R Open or R Server distribution due to better compatibility and availability of packages and features. You can find my suggested version of R Open available for download from the MRAN page.

For better working with R, you can install R Studio. Alternatively, if you are crazy about Visual Studio, then you can find the R Tools For Visual Studio add-on that will also let you work more efficiently with this language.

Afterwards, you have to configure Power BI to enable R script and indicate the R path on local disc. If you’ve installed R IDE, you can also select it from the drop-down list in the settings panel. It will be necessary for moving contexts between Power BI and R IDE.

Below you can see where and how to configure R scripts in Power BI:

Configuring R in Power BI

Now you are ready to start your journey with Power BI and R.

R script as a data source

With this functionality, you can connect to a data source regardless of whether it is in an available range of built-in connectors or not. The only limitation we encounter is the range of existing sources in R and as you know, that range is very wide.

To connect to a source directly using R Script, click Get Data > Other > R script.

Importing R script to Power BI

This will open a window where we can paste the R script. For this article we will use a sample script that retrieves PKN Orlen’s company stock data directly from stooq.

Note that in one script we can include all the processing logic. This way we conveniently have one central place of editing and processing a particular data source. Not only can we edit metadata at this stage, but we’re also able to create additional variables as part of the feature engineering.

Once you have accepted the R script as a data source, the data will be imported and we will be ready to quickly create visualizations, including predictive analytics, through the Analytics Toolkit in Power BI.

Below is an example of PKN Orlen closing price visualization and predictions of future prices.

Closing price history and prediction graph

As you can see, this combination of tools can give you not only a visualization of existing data, but also a trend analysis. You can use the prediction function to plan your business actions with reasonable confidence. Likewise, you can assess whether the current course will keep your business on track.

R script as a processing script

Another way to use R in Power BI is in the context of data transformation, as a step in the entire data editing flow; a prevalent and well-known reason for using it is to fill in the missing values in a dataset.

Calling the mice package is an advanced way of taking care of missing values. You can use different models according to which data will be filled. There are many options for this, from the popular Predictive Meaning method, through to the advanced Random Forest imputations. Below is an example of calling R script in this case.

WHAT YOU SHOULD REMEMBER!

By default, remember that input is always a data frame named dataset. The final dataset that is on the input should be called output.

Script for the mice package to handle missing values

R script as a visualization

The most common and best-known way to use R in Power BI is to use it in the context of visualization. With R Visuals we have the ability to extend the range of built-in options. We also gain the ability to build visualizations tailored to the needs of the recipient.

A very common case in this area is to use a graph to present correlations from the corrplot package. This makes it easy to visualize how one variable depends on another. This graph allows you to illustrate a positive and negative relationship. Moreover, you can modify the presentation method for this dependency – either graphically or numerically.

The graph below is a response to the above script. It shows the correlation between sales, marketing and weather conditions. You can see the positive relationship between sales and marketing and more importantly, between sales and weather.

A three-factor correlation graph

The Power BI script editor window is equipped with some very useful functionalities that allow us to move the R script context to an external IDE such as R Studio.

The context transfer procedure is very convenient for the user, because the dataset is generated along with the data input that would be in Power BI. You can move the context to the IDE using the arrow in the editor bar. See below:

Switching the script context to an external IDE

Great! But what about creating custom visualizations?

Many custom visualizations have been created in the Power BI Community and a lot of them are based on R. These are boxed solutions – just import them from the pbiviz file.

However, what if we wanted to create our own visualization and pack it into a box so that it can be easily transferable and deployable for other users? It is possible! And here’s how.

R script as a part of Power BI visualization tools

Below I have prepared a tutorial showing how to easily create your own visualizations – so you will understand how to approach this task.

In this video you will find:

1. Information about what you need to prepare to build power packs with visualization in Power BI
2. Wider opportunities associated with Power BI
3. Breaking the limitations and differences between Power BI and Tableau
4. An instruction on how to build a fully interactive (HTML) scatter plot derived from the Plotly package.

But first, here are the instructions for preparing the environment for working with this visualization package:

1. Install R and R Studio
2. Download R script with Scatter Plot visualization (below)
3. Install Power BI visualization tools – don’t worry, the tutorial includes guidelines!

source('./r_files/flatten_HTML.r')
############### Library Declarations ###############
libraryRequireInstall("ggplot2")
libraryRequireInstall("plotly")
###################################################
############### Actual code ###############
# Let's use the diamonds data set :
d <- diamonds[sample(nrow(diamonds), 1000), ]
# Make a basic scatter plot :
p=plot_ly(d, x = ~carat, y = ~price, type="scatter", text = paste("Clarity: ", d$clarity),
          mode = "markers", color = ~carat, size = ~carat)
internalSaveWidget(p, 'out.html')
###################################################

Build powerful visualizations with Power BI, R and Plotly

Next steps

Hopefully you will now have a better understanding of the integration between R and Power BI. We have gone over implementation, suggested applications and creating custom visualizations. These aspects can significantly increase your data analytics capabilities.

You can now find hidden trends and gain predictions from your data, and customize your view to see the information of the most interest. Then, you can use these insights to plan a well-informed business strategy that is based on relevant factors, or revise it accordingly.

I would be happy to share my experience of these tools, so if any of these areas require more clarification, or you’d like to know more, just get in touch!!