Customize Interactive R Visuals in Power BI

dataViz

Published

December 1, 2017

Some of us, through no fault of our own, have to work with things like Power BI. While it’s a powerful application, it’s just a little…you know. For anybody who works with R, Python or JavaScript or anything like that, it just feels like closing the black box a bit, not to mention reproducibility problems. The good thing about Power BI is that you can hack at it a little, and we can get the majority of R’s graphing power into it. It’s actually pretty straightforward to do, the only tricky thing being when the plot doesn’t come out like you expected – then you’ll have to retrace some steps. But otherwise, all you need is some familiarity with the command line, and now that RStudio has a terminal built in, you’ve got no excuse!

Step 1

So first you need to download and install Node.js if you don’t have it already. The easiest thing is to download the installer from https://nodejs.org/en/download/ for your operating system.

Step 2

Now you can install the Power BI command line tools. Here Power BI gets moderately interesting! In your terminal, type npm install -g powerbi-visuals-tools. You can check it worked with the command pbiviz. It should print this:

$ pbiviz

     +syyso+/
    oms/+osyhdhyso/
    ym/       /+oshddhys+/
    ym/              /+oyhddhyo+/
    ym/                     /osyhdho
    ym/                           sm+
    ym/               yddy        om+
    ym/         shho /mmmm/       om+
     /    oys/ +mmmm /mmmm/       om+
    oso  ommmh +mmmm /mmmm/       om+
   ymmmy smmmh +mmmm /mmmm/       om+
   ymmmy smmmh +mmmm /mmmm/       om+
   ymmmy smmmh +mmmm /mmmm/       om+
   +dmd+ smmmh +mmmm /mmmm/       om+
         /hmdo +mmmm /mmmm/ /so+//ym/
               /dmmh /mmmm/ /osyhhy/
                 //   dmmd
                       ++

       PowerBI Custom Visual Tool



  Usage: pbiviz [options] [command]


  Commands:

    new [name]        Create a new visual
    info              Display info about the current visual
    start             Start the current visual
    package           Package the current visual into a pbiviz file
    validate [path]   Validate pbiviz file for submission
    update [version]  Updates the api definitions and schemas in the current visual. Changes
the version if specified
    help [cmd]        display help for [cmd]

  Options:

    -h, --help      output usage information
    -V, --version   output the version number
    --install-cert  Install localhost certificate

Step 3

Now we can create our custom R visual. First, create a new folder where you can put all of these visuals (not necessary, just handy), and move there with the command line.
Now we can create the R template. From this, we only need to change the R code and a few small things to get our own customized interactive R visual. To create the template, type pbiviz new sampleRHTMLVisual -t rhtml in the terminal. This should create a folder, ‘SampleRHTMLVisual’. Inside it will be a bunch of files, and the majority of these we don’t need to touch.

Step 4

There is one file that we need to change, script.R, which contains the R code used to create the R visual that Power BI will use. In this script, the data we use is called ‘Values’ (it will always be called ‘Values’, regardless of the original name of the dataset in Power BI – this is just a placeholder name that Power BI uses. This can be changed in the capabilities.json file, but I haven’t tested this, there may be other references to ‘Values’ elsewhere). This is a regular R script like any other, with some sandbox exceptions. For example, I used the following code to create a boxplot for one variable:

source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("ggplot2");
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################
q <- ggplot(Values, aes(y = dias_atraso, x = 1, label = dias_atraso)) +
  geom_boxplot(colour = "#A60822") + xlab(NULL) + ylab(NULL) +
  theme_minimal() + coord_flip() +
  theme(axis.text.y = element_blank())
####################################################

############# Create and save widget ###############
p = ggplotly(q, tooltip = c("label"));
internalSaveWidget(p, 'out.html')
####################################################

The only parts I changed were the ‘Actual code’ part, and tooltip = c("label") in the call to ggplotly(). The rest came straight from the template.

The result of this can be seen in the image below. The plot is interactive, and values are highlighted when the user passes the cursor above it. It’s a nice simple toy version of a boxplot, in my opinion much better than the options for boxplots in Power BI.

Step 5

On the command line, move to inside the SampleRHTMLVisual folder (cd SampleRHTMLVisual). Then type pbiviz package to build the visualization file.

Step 6

This file (named SampleRHTMLVisual.pbiviz, you can find it in the ‘dists’ folder) can now be selected from Power BI with ‘Import a Custom Visual’ > ‘Import from File’. There you go! Don’t forget, here we created a mega-simple little boxplot, but the method is the same for whatever you want to do. For example, if you wanted to use rstan to run a quick model and plot some results, the method is the same (will take a little longer to load, of course…).

Some Comments

These visualizations can be a bit of work to debug – if something goes wrong, you will need to:

A - remove the visual and delete the imported custom visual from the sidebar
B - correct the R code
C - build the package again
D - import the new visual into Power BI.

The above ‘template’ approach works the first time, but if you repeat it you will have two SampleRHTMLVisual folders. To avoid this, just copy and rename the template folder we created. After you rename it, you will need to replace the name, displayName and guid variables in pbiviz.json. For example:

"name": "sampleRHTMLVisual",
"displayName": "sampleRHTMLVisual",
"guid": "sampleRHTMLVisualD10B081869514400A096DAB09C8B2634"

were renamed to:

"name": "boxplot",
"displayName": "Box Plot",
"guid": "boxplotlD10B081869514400A096DAB09C8B2634"

You will also have to rename the references to the SampleRHTMLVisual folder in the node_modules > package.json file.

Having more than one of these visuals in your PowerBI workspace can also be confusing, as they will all have the same icon in the sidebar. You can change this by downloading an icon png file and resizing it to 20 x 20 pixels. Name this file icon.png and place it in the assets subfolder of your visual folder.

There are tutorials here on building custom visualizations, including for R, based on the Typescript superset of JavaScript. Happy PoweR Bi-ing!