In this short post, I will describe how to show text results from an R script visual in Power BI.

Microsoft Idea

Today there is already an idea submitted to Microsoft to enable this possibility however this idea does not have a lot of votes so it is not likely to be added anytime soon so then the workaround comes to the rescue!

Why do we even need to show text result from an R visual?

According to the multiple comments submitted in the “Microsoft Idea”, the main reason is to show the result of a statistical test or summary information of a model. (And this is exactly what I’m using it for too)

Here are some comments submitted to Microsoft:

• “R Integration is fantastic… but seeing the console output in a visual is still needed to see all the coefficients and model stats etc...”
• “Would be great to have this as a simple way to get results of statistical tests.”
• “That would be a fantastic possibility,I need to just show the summary of a regression model!“
• “…I’m working with forecasts as well as predictive models and need to be able to print results…”

The multiple solutions

The solutions that I will describe in this post are a workaround so it is not as straightforward as just outputting the result of the statistical test or model summary. It requires some text manipulation but is still pretty quick to implement.

Let’s say we want to run a t-test in Power BI using R and see the result in a visual.

If we were to write the R code in Rstudio and run it the output of the t-test will look like this:

# dataset <- unique(dataset)
res<-t.test( dataset$ColumnA,dataset$ColumnB, paired = TRUE, 
             alternative ="less" ,conf=0.95)
res

However, when we run the same code in an R visual in Power BI the output is as follows:

As we can see we get an error message “The R code didn’t result in creation of any visuals”. Unfortunately at the time of writing this post, it is not possible in Power BI to display the R console result.

And according to the error message we cannot simply print the result, however, we can “plot” it as long as an image is created so let’s do it!

The use of GGplot

GGplot is one of the most famous R libraries for creating graphics.
Now what we need to do before plotting the t-test output is to :

• First store the result of the statistical test or pedecitive model into a variable
• From this variable we can retrieve each coeeficient that we want to show
• Create a new text variable where we concantenate and indent all the information that we want to display
• Plot this variable using ggplot library with the annotate function and the magic happens

# dataset <- unique(dataset)
library(ggplot2)
res<-t.test( dataset$dem_percent_12,dataset$dem_percent_16, paired = TRUE, 
             alternative ="less" ,conf=0.95)
text = paste("R result:","\n",
             "method: ",res$method ,"\n",
             "alternative: ",res$alternative ,"\n",
             "T: ",res$statistic ,"\n",
             "p-value: ", res$p.value,"\n",
             "Confidence Interval:","95%","\n",
             "CI Low: ",res$conf.int[1],"\n",
             "CI Up: ",res$conf.int[2],"\n")

ggplot() + 
  annotate("text", x = 0,5, y = 0, size=6, label = text, hjust = 0) + 
  theme_void()

The use of grid

Another solution is to use the library Datagrid this solution is a bit tidier and neater.
I may be old fashion but I actually prefer a simple text output using the ggplot approach above.
Here the approach is very similar but instead of simply concatenating the t-test result variables in a text variable we create a data frame and create a row for each variable that we want to display.

library(gridExtra)
library(grid)
res<-t.test( dataset$dem_percent_12,dataset$dem_percent_16, paired = TRUE, 
             alternative ="two" ,conf=0.95)

Name <- c("method", "alternative", "T-stat", "P-value")
Value <- c(res$method, res$alternative , res$statistic, res$p.value)

df <- data.frame(Name, Value)
tt <- ttheme_default(colhead=list(fg_params = list(parse=TRUE)))
grid.table(df, theme=tt)

The use of Gtable

And finally, the last solution that I can think of is to use the library Gtable which is very similar to the library grid but it has the advantage to be very flexible, more customizable and looking even nicer however it is less straightforward to implement.

library(gtable)
library(gridExtra)
library(grid)
res<-t.test( dataset$dem_percent_12,dataset$dem_percent_16, paired = TRUE, 
             alternative ="two" ,conf=0.95)

Name <- c("method", "alternative", "T-stat", "P-value")
Value <- c(res$method, res$alternative , res$statistic, res$p.value)

df <- data.frame(Name, Value)
g <- tableGrob(df ,rows = NULL)
g <- gtable_add_grob(g,
                     grobs = rectGrob(gp = gpar(fill = NA, lwd = 2)),
                     t = 2, b = nrow(g), l = 1, r = ncol(g))
g <- gtable_add_grob(g,
                     grobs = rectGrob(gp = gpar(fill = NA, lwd = 2)),
                     t = 1, l = 1, r = ncol(g))
grid.draw(g)

Here you find all the details on how to use the libraries Grid or Gtable.

Show text results from an R visual in Power BI

And here is how the R visuals render in Power BI so depending on the desired size of the visual you may need to tweak the theme’s parameters.

Conlusion

As seen in this short post even though it is not possible at first to show text from a test result of a statistics test or summary of a statistics model like a regression in an R visual. But with this workaround, we can quickly retrieve the variables that we want to show and display them.

This may be a bit of tedious work for a large model with a lot of variables to output but I’m not sure either that running a large predictive model in Power BI would be the right thing to do.