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 visualisation. To help answer some of them, we 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 visualisation. However with the help of R, many of these restrictions disappear.R allows you to create highly customisable visualisations, 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
- Visualisation – extend the range of visualisations available in R and implement charts
- As a part of Power BI visualisation – gain the ability to build R-based visualisations 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 our 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.
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. 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 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 visualisations, including predictive analytics, through the Analytics Toolkit in Power BI. Below is an example of PKN Orlen closing price visualisation and predictions of future prices. 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.
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.
R script as a visualisation
The most common and best-known way to use R in Power BI is to use it in the context of visualisation. With R Visuals we have the ability to extend the range of built-in options. We also gain the ability to build visualisations 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 visualise 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. 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.
Next steps
Hopefully you will now have a better understanding of the integration between R and Power BI. We have gone over implementation and suggested applications. These aspects can significantly increase your data analytics capabilities.You can now find hidden trends and gain predictions from your data, and customise 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.We would be happy to share our experience of these tools, so if any of these areas require more clarification, or you'd like to know more, just get in touch.
Author
