Introduction

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 in the local machine where we have installed Power BI desktop.

Continue with the installer instructions to set up 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 set up 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, 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 customizations that we have specified in the Query as shown below.

install.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),
 
 axis.text.y=element_text(colour="grey20", face="bold", hjust=1, vjust=0.8, size=25),
 
 axis.title.x=element_text(colour="grey20", face="bold", size=26),
 
 axis.title.y=element_text(colour="grey20", face="bold", size=26))

Executing the scripts will yeild 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.

 

Reference

More details about this topic can be found from the below links as well :