Create Business Intelligence Reports From SharePoint Online List Using Power BI And R Open

Power BI is a cloud based service that provides data visualization options based on the source data. Using Power BI, we can create dashboards based on the data. In order to create reports using Power BI, make sure that you have a Power BI account.

Power BI has the capability to connect to various data sources to derive data and create business intelligence reports. In this article, we will see how to import SharePoint Online List data to Power BI, and use R Programming within Power BI to create a bar chart business intelligence report out of the SharePoint data.

Prerequisites

SharePoint Online Subscription
Power BI Desktop
Microsoft R Open

SharePoint Online Subscription

If you don’t have an Office 365 SharePoint Online Subscription, you can get a trial version from here.

Power BI Desktop

If you don’t have an account, you can sign up for it here. You can either work with Power BI online or download the Power BI Desktop version from here. Once downloaded, you can install it in the local machine.

Microsoft R Open

Microsoft R Open is the distribution of R from Microsoft Corporation. It is a complete open source platform for statistical analysis and data science. You can download it from here.

Once downloaded, install it on the local machine where we have installed Power BI desktop.

Continue with the installer instructions to setup R Open.

Specify the location where R Open would be installed in the local machine.

Take a note of the location. The default location is - C:\Program Files\Microsoft\MRO-3.3.1\

Configure R Open in Power BI

Once R open has been installed in the local machine, copy the path where R was installed. We will have to setup the R open location within Power BI desktop. Select options from Power BI.

From the left pane, select R Scripting.

Clicking on OK will open up the R Script Options page. Specify the R Open location in the text box.

This completes the configuration of R open within Power BI. We are good to go ahead and create business intelligence reports using R Open and Power BI.

Generate Power BI reports

Select "Get Data" option from Power BI. This will enable us to connect to various data sources.

We will choose "SharePoint Online List" as we have to derive the data from the list.

Specify the SharePoint Online List URL and click on OK.

Click on "Sign in" to connect to the SharePoint Online Site.

Specify the registered Office 365 credentials.

Once we are signed in, click on "Connect" to complete the connection to the SharePoint Site.

We will be connecting the Product Sales List which contains quarterly sales result of products.

The SharePoint List will contain the below data.

Once connected, the above data will be imported to Power BI. A sample table of the data is shown in Power BI, as below.

Clicking on "Load" will load the data into Power BI. All the fields that are imported along with the data will be shown in the right pane.

We can delete the unwanted columns from the Power BI data import, as shown below.

Finally, we have the required refined data.

But in order to make it useful to create a Power BI report, we will have to change its data type. Currently, it is text; we will be changing it to number.

Select "Whole Number" option.

In order to visualize the data using R, we will select the R option from the visualization options.

Click on "Enable" to enable script visuals.

This will open up the R Script Editor as well as the Visualization Display.

Drag and drop "Product" and "Quarter 1" to Values and Visual level filters section.

The next step is to plot the bar chart using R Script.

We can use the below command to create a bar chart with Quarter 1 along the Y Axis and Product along the X axis.

barplot(dataset$Quarter1, names.arg=dataset$Product)

Executing the script will generate the below bar chart in Power BI.

The out of the box representation is quite plane. So we can use ggplot which is much more customizable and has better graphic display options. Run the below command to install ggplot.

install.packages("ggplot2")

In case you come across some error as shown below, it is mainly because of right permission issues in the R Open installation folder.

In order to resolve this, you can head over to the R Open installation folder and grant full control to the current user.

Once it is done, running the below command will install ggplot2 and set up the bar chart based on the customization that we have specified in the Query, as shown below.

packages("ggplot2")
library(ggplot2)
ggplot(dataset, aes(x=dataset$Product, y=dataset$Quarter1)) + geom_bar(stat="identity",colour="black", size=2) +
labs(x="Car", y="Total Sales")+theme(axis.text.x=element_text(angle=90, colour="grey20", face="bold", size=25),
text.y=element_text(colour="grey20", face="bold", hjust=1, vjust=0.8, size=25),
title.x=element_text(colour="grey20", face="bold", size=26),
title.y=element_text(colour="grey20", face="bold", size=26))

Executing the scripts will yield the below bar chart.

Summary

Thus, we saw how to import SharePoint Online List data to Power BI and generate business Intelligence reports using R Open.