R graphs and tables in Power BI Desktop

Power BI Desktop enable users to use R script visual for adding custom visualization generated with R language – regardless of R package used. Before using R script visual, you will need to enable it by setting path to R Engine on your client in the global options. Once this is done, you will be able to enhance your Power BI reports using R visualizations.

My personal favorite R packages I like to use in Power BI are shiny, plotly, ggplot2, googleVis, ggiraph, rCharts and d3js.  Apart from finding the package for data visualization where you feel home, one of the very great thing I find with Power BI + R is the ability to have visualizations created dynamically (more or less like if you would be using plotly or shiny)  with slicers and selectors. And this makes data exploration, data wrangling so much easier.

With October 2016 Power BI custom visuals got additional 7 R visuals to the family (check Power BI Visuals Gallery and fall in love with your favorite one 🙂 ).

Upon using any of the additional R visuals, Power BI will – prior to importing the additional visualizations – ask for installing all the missing packages, as well as inform you of any incompatibility between R engine and package.

So how easy is to create R visualization in Power BI?  Let us first import data to Power BI from SQL Server. I have generated a sample data-set for this demo. Data and all the samples are available at Github.

With sample data-set I have created a simple data visualization with slicers using Power BI.

Now, the same visualization using R.

Note that I have used same slicers (Year = 2016, Month = 2) and R graph (using ggplot2) was able to dynamically  visualize the numbers. So this mean, that any R visualized graph can be navigated and dynamically updated, which is absolutely great, especially in cases when you do data exploration. Following R code was used:

library(ggplot2)
ggplot(data=dataset, aes(x=Day, y=Value)) +  geom_line(aes(color="red")) + 
ggtitle("Value by day") + theme(legend.position = "none") + 
theme(panel.background = element_rect(fill = "white", color = "lightgray", size = 1))

with additional dataset conversion to data.frame done automatically.

Where it get’s tricky is if you want to return the R data.frame into your Power BI page in form of a table (imagine you want some results to your graph or that you are calculating some statistics). Well, Power BI does not allow returning tables from R script visual. Unless, table is an image (that is, in a “form” of a visualized “graph”).

So I manage to create a table as a graph using R and gplot function from ggplot2 package.

The best part of this table is, that is dynamically changed based on selection on graphs or slicers. The downside is, that it can not be scrolled within the object. So if you end up with bigger table, you have to tweak the font size or show table aggregated or grouped.  In my opinion, I would not change R created table with Power BI table, because Power BI table is simply better and easier to create and maintain. But I would definitely recommend using R tables to support your R visuals. For example: If you are generating clusters using R in power BI, use R generated table to expose additional statistics on clusters.

The code for generating this R table is:

library(gridExtra)
library(ggplot2)

qplot(x=1:5, y=1:5, geom = "blank") + 
theme(line = element_blank(), text = element_blank()) + 
annotation_custom(grob=tableGrob(dataset))

Script generated blank plot, where custom annotation is added with function grob. And this is our data.frame from dataset and is displayed on top of empty graph.In addition to “remove” the background blank graph, blank line and text is added:

theme(line = element_blank(), text = element_blank())

 

Update #1 (19.12.2016)

After reading the comment to this blog post from reader Baptiste Auguie  (with link to his post on displaying tables as grid graphs), that using grid will return the same result, but very much a simpler solution to plotting / drawing table. Thank you Baptiste! I must have missed this one.

R code is far less simpler without  qplot function.

library(gridExtra)
grid.table(dataset)

And the printscreen from Power BI:

As always, sample, code and data-set is available at Github.

Happy PowerBI-Rrrring!

‹ SQLSatSlovenia 2016

SQL Saturday Vienna 2017 #sqlsatVienna ›

Tagged with: graph, image, Microsoft, Power BI, R, table, Visualization
Posted in Uncategorized