Power BI – Create Custom Interactive Visuals with R


Prerequisites

Install node.js: Download and Install the latest version of node.js from  the node.js website

Install R : Download and install the latest version of R from here.
Once R is installed I also recommend you to install R Studio the integrated development environment for R. 

Enable R scripting in Power BI: In Power BI desktop, go to File > Options and settings > Options>R Scripting: Then set up your R home directories and R IDEs.

Set up Node js

Open a CMD command and type:

npm install -g powerbi-visuals-tool

To check if the Powerbi-visuals-tools has been installed successfully installed type:

pbiviz 

Let’s build our first R Custom Visual with HTML output

Now using the windows “cd” command navigate to the specific folder where you want to store your custom PBI visuals and type:

pbiviz new sampleRHTMLVisual -t rhtml

You should now see a similar folder named sampleRHTMLVisual created in your specified folder

If you open the file Script.r you’ll see the below R script generated.
You can find this template from the Microsoft github PowerBI-visuals

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

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

################### Actual code ####################
g = qplot(`Petal.Length`, data = iris, fill = `Species`, main = Sys.time());
####################################################

############# Create and save widget ###############
p = ggplotly(g);
internalSaveWidget(p, 'out.html');
####################################################

You can change this script later to create your own visuals. I shall write soon another article about how to create nicer and more advanced visuals in R.

Generate the PowerBI visual file:

Now we need to create the pbi file, still in the CMD command using “cd” navigate to the folder where the script.r is stored and type:

pbiviz package

Once the command has been executed you should see the PBI file in the “dist” folder:

Import the custom visual into PowerBI

Now in PowerBI in the visualizations pane we need to click on the three dots > Import from file and select the PBI file located in the “dist” folder.
Then even though the R script references a sample dataset and not the Power BI data we need to add a measure to the Values area to trigger the visual to appear.

Note that the interactions with this visual are limited as this is an HTML
visual. The main limitation is that you cannot interact with the other visuals present in your PowerBi page.
However we still get few interactions such as:

  • tooltip
  • zoom in/out
  • auto scale
  • display/hide categories
  • compare data on hover…

If you are already proficient in R you can easily create other visuals you just need to edit the script.r file, run the pbiviz package command again and import the generated PBI file.

I’ll be shortly writing another post on how to create more advanced visuals and how to use data directly from PowerBI instead of a preloaded sample dataset.

Also, if you’re fluent in JavaScript and Json you can create even more advanced custom visuals that can fully interact with your other visuals just like the built-in powerbi visuals.


Leave a Reply

Your email address will not be published. Required fields are marked *