• Irene Librenti

How to create Power BI interactive custom visuals using R (html) - Part 1

Updated: Nov 3, 2020

Introduction


Power BI offers a pool of default visual possibilities, further enriched by the marketplace, where more options are available.

However, we can receive very challenging and demanding business requests and find ourselves in need of more powerful and customizable solutions for our customers.

R visuals help us in this effort and with R we are able to create complex graphical representations of data.


The simplest way of implementing R visuals into your Power BI reports is to import the R script visual component and type your script, as seen in this example below:


ggplot(data=dataset, aes(x = Sepal.Length, y = Sepal.Width)) +
geom_point(aes(color=Species, shape=Species)) +
 xlab("Sepal Length") + ylab("Sepal Width") +
 ggtitle("Sepal Length-Width")

Creating Power BI visuals using R component


However, even though this is the quickest approach, it is not the recommended one. Once the report is deployed, anybody could potentially open the visual component and accidentally modify the script, causing the visualization to fail. In addition, the script as such is not reproducible using other datasets, as it contains hardcoded column names originating from the report data model.

A better approach is to use R Powered visuals, as they allow for encapsulating the R script and presenting it to customers as a regular visual component.

Prerequisites

To start building your R custom visual you will need to:


- Download and install Node.js from https://nodejs.org/en/download/

- Install Power BI visual tools by opening you command prompt and typing the following:

npm install -g powerbi-visuals-tools

After the installation has terminated, type pbiviz in the command prompt

Power BI custom visual tool installation

Now that everything is in place, we are ready to go!

Visual Development


Get the template

Now, it is time to start building your first R HTML visual. I created a folder called R_Visuals and navigated inside of it by typing:

cd R_Visuals

You then need to type the following in the command prompt:

pbiviz new sampleRHTMLVisual -t rhtml

A new folder is created and inside it you will find a file called script.r. The file contains this script:

The script is divided into three parts:

  1. Library installation

  2. Actual plot code

  3. Plot transformation to a html widget

As you can see in this sample script, a plot is created using the iris dataset. Now, the visual needs to be deployed by navigating inside the visual folder and typing:

pbiviz package 

Everytime we mofidy our visual, we will need to type this command to save our changes.



We will encounter the following error messages:


To correct these issues we just need to open the file pbiviz.json, fill in the missing fields and save our changes.


Let’s see what happens when we import this visualization into our Power BI report. Navigate to the folder dist. Inside you will find a file with the extension .pbiviz. Open it and this will be the result:




Click on your new visual and drag at least one column from the table Iris. Which column you choose is not important per se, because the dataset is hardcoded in the script, but it triggers the rendering of the visual.

You should now be able to see the visual in your report canvas.


Create your script

The next step is to generalize the variables of our script, so that the plot will show the values coming from your Power BI data model.


Let’s go back to our visual folder and open the R script. As we mentioned above, it contains the code for a plot with the hardcoded iris dataset inside.

Replace the plot code with the code below. Note that in this new code I replaced iris withValues.

g = ggplot(Values, aes(x = Values$Sepal.Length, y = Values$Sepal.Width)) +
 geom_point(aes(color=Values$Species, shape=Values$Species)) +
 xlab("Sepal Length") + ylab("Sepal Width") +
 ggtitle("Sepal Length-Width")

If you save the visual and import it again into your Power BI report, you will notice that the plot is rendered only if you pass Sepal.Length, Sepal.Width, and Shape to the visual, as our script is not yet fully generalized.


The next step is to open the file capabilities.json (find out more), where you will see all fields defined in your visual. You will notice that the only “data role” available is Values that is the only input that the script can receive.


Let’s replace Values with the desired input fields for our visual, in this case: X axis, Y axis, and Legend.


 "dataRoles": [
        {
            "displayName": "X",
            "kind": "GroupingOrMeasure",
            "name": "X"
        },
        {
            "displayName": "Y",
            "kind": "GroupingOrMeasure",
            "name": "Y"
        },
        {
            "displayName": "Legend",
            "kind": "GroupingOrMeasure",
            "name": "Legend"
        }



"select": [
{
"for":	{
	"in": "X"
	}
},
{
"for":	{
	"in": "Y"
	}
},
{
"for":	{
	"in": "Legend"
	}
}
]

Once done, return to script.R and replace the plot code with this code:

Values <- data.frame(X,Y,Legend)

names(Values)[1] <- "X"
names(Values)[2] <- "Y"
names(Values)[3] <- "Legend"

g = ggplot(Values, aes(x = X, y = Y)) +
  geom_point(aes(color=Legend, shape=Legend))

First I created a dataset with the input fields listed above with the corresponding column names. In the plot code, I simply replaced the existing column names with the new column names. Save the file, open the command prompt, and deploy your changes. You can now import your visual into Power BI.

Finalize the visual


Now that your custom visual is fully working, we need to finalize our customization. We will replace the existing icon with a customized one. To do so, navigate to assets where you will find the icon currently used in your visual. Replace it with a new personalized icon, making sure it is 20x20 px. Once your icon is replaced, it is time to rename the visual. You can move to the pbiviz.json file and modify name, displayName, and guid.

Deploy the visual and there you go. Congratulations for developing your first custom visual!



If you want to dig deeper into how to further customize your visual, tune in the next few weeks and subscribe to our newsletter.

733 views

Address

Winghouse

Ørestads Boulevard 73

Copenhagen, 2300

Denmark​​

Contact

+45 91101130

+45 60907466 ​

info@nordone.dk

© 2020 by NordOne, Copenhagen Denmark