HTML widgets in Power BI | R-bloggers

HTML widgets in Power BI | R-bloggers

HTML widgets in Power BI
Posted on November 23, 2022 by Stéphane Laurent in R bloggers | 0 Comments
[This article was first published on Saturn Elephant , and kindly contributed to R-bloggers ]. (You can report issue about the content on this page here )
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
It is possible to include a htmlwidget in a Power BI report, such as a plotly graphic.
Setting up an environment
You first have to follow the first three steps of this tutorial :
install Node.js;
create and install a certificate;
install the npm package powerbi-visuals-api.
For Windows, you can download a portable version of Node.js on the nodejs.org website: choose the 64-bit zip, unzip it in a folder and set this folder in your Windows path.
Follow the tutorial for steps 2 and 3. Note that you have to perform step 3 only once, there’s no need to generate a new certificate for each visual you will create.
For step 4, run npm install -g powerbi-visuals-api in a terminal.
Example
Let’s assume you want to render the interactive graphic produced by this code:
library(plotly) gg <- ggplot(iris, aes(x = Petal.Length, y = Sepal.Length, color = Species)) + geom_point() ggplotly(gg)
create a working directory: mkdir pbiwidgets and go into it: cd pbiwidgets;
initialize a new visual by running: pbiviz new iris -t rhtml;
this creates the folder pbiwidgets/iris with a lot of stuff;
in this folder, edit the file pbiviz.json; you have to write something for description (what you want), supportUrl(write anything you want, e.g. https://www.example.com), and you have to write something for the fields name and email;
now you have to edit script.r:
source('./r_files/flatten_HTML.r') ############### Library Declarations ############### libraryRequireInstall("plotly") #################################################### ################### Actual code #################### gg <- ggplot(iris, aes(x=Petal.Length, y=Sepal.Length, color = Species)) + geom_point() p <- ggplotly(gg) #################################################### ################## Save widget ##################### internalSaveWidget(p, "out.html") #################################################### ################ Reduce paddings ################### ReadFullFileReplaceString( "out.html", "out.html", ',"padding":[0-9]*,', ',"padding":0,' ) ####################################################
the dataframe Values is the dataset which will be sent to this R script by Power BI;
since we want to use iris as data, we save it to an Excel file that we will import in Power BI; I recommend to use writexl (the Excel files written with openxlsx cause an issue when one tries to load them in Power BI):
writexl::write_xlsx(iris, "iris.xlsx") # or writexl::write_xlsx(list("sheetName" = iris), "iris.xlsx")
go to the folder pbiwidgets/iris in your terminal and run the command pbiviz package;
now open Power BI, import iris.xlsx, select and load the sheet;
in the ‘Visualizations’ panel, click the three dots, then ‘import a visual from a file’, and select the pbiviz file which is located in pbiwidgets/iris/dist
a new icon showing some tools appears at the bottom in the ‘Visualizations’ panel, click it;
in the ‘Fields’ panel, select the columns used for the plot, here Petal.Length, Sepal.Length and Species;
that’s it, you get the interactive graphic in Power BI.
Helper function
If you have to deal with multiple widgets, you will be bothered about the filling of the pbiviz.json file. Here is a R function which fills it for you:
library(jsonlite) fill_pbiviz <- function(path)
Just run fill_pbiviz("path/to/pbiwidgets/iris").
Notes
This works very well with plotly and ggplotly widgets. I also tested a graphic with the scatterD3 package and the result was fine as well. But this can be less fine for other htmlwidgets.
For example, with the ggiraph package, I managed to achieve a good result after settings the options width_svg = 3, height_svg = 2.5 in the girafe function. I also set the option opts_zoom(min = .7, max = 2) in the girafe_options function:
gg_point <- ggplot(data = data) + geom_point_interactive(...... p <- girafe(ggobj = gg_point, width_svg = 3, height_svg = 2.5) p <- girafe_options(p, opts_zoom(min = .7, max = 2)) internalSaveWidget(p, "out.html")
I tried a pie chart with the rAmCharts4 package and got an empty plot. I finally managed to get the pie chart by removing the last lines of the file script.r:
################ Reduce paddings ################### ReadFullFileReplaceString( "out.html", "out.html", ',"padding":[0-9]*,', ',"padding":0,' )
And I set the height option of the amPieChart function to 300px, otherwise the legend was truncated.
Advanced example
When you select the columns Petal.Length, Sepal.Length and Species, they appear in the ‘Visualizations’ panel, under the dataset name Values:
Then, Power BI sends the data to the R script with the help of a CSV file, and the columns of this CSV file are in the same order as your selected columns.
Therefore, instead of using the column names in the R script, one can use the column indices.
Let’s illustrate. We will create a scatter plot as in the previous example, but the user will have the choice of the variables on the x-axis and the y-axis, and he’ll can also choose or not a color factor.
Firstly, we change the display name of the Values dataset. Open the file pbiwidgets/iris/capabiities.json (with any text editor), and change the display name in the dataRoles field:
], ......
Now we will see Data (x, y, by) in the ‘Visualizations’ panel, instead of Values:
Then we use the following R code in script.r:
################### Actual code #################### nms <- colnames(Values) ncols <- length(nms) if(ncols == 2L)else if(ncols >= 3L)else p <- ggplotly(gg) ####################################################
Now we can choose the columns we want for the x-axis and for the y-axis, and we can optionally choose the Species color factor:
Arbitrary HTML
In fact, pbiviz is not restricted to render a htmlwidget. One can render any HTML file, it suffices to save it under the name out.html in the R script. For instance, in the above example, we render a blank plot if the user does not select at least two columns. We can throw a message instead, with a R script like this:
source("./r_files/flatten_HTML.r") ############### Library Declarations ############### libraryRequireInstall("plotly") #################################################### htmlMessage <- function(message) ################### Actual code #################### nms <- colnames(Values) ncols <- length(nms) if(ncols == 2L)else if(ncols >= 3L)else ################## End of script ###################
Using multiple datasets
Some graphics require more than one dataset. We give an example here.
First, we prepare two datasets:
library(dplyr) data("diamonds", package = "ggplot2") ## creating dataset #1 data1 <- diamonds %>% group_by(clarity) %>% summarize(m = mean(price)) ## creating dataset #2 data2 <- diamonds %>% group_by(clarity, cut) %>% summarize(m = mean(price)) ## save to Excel files writexl::write_xlsx(list("data1" = data1), "diamonds_summarized1.xlsx") writexl::write_xlsx(list("data2" = data2), "diamonds_summarized2.xlsx")
Create the visual project: pbiviz new diamonds -t rhtml. Complete the file pbiwidgets/diamonds/pbiviz.json as previously explained, and modify the file pbiwidgets/diamonds/capabilities.json as follows:
, ], "dataViewMappings": [ }, } ], ......
Now the R script, in pbiwidgets/diamonds/script.r:
source('./r_files/flatten_HTML.r') ############### Library Declarations ############### libraryRequireInstall("plotly") #################################################### ################### Actual code #################### gg <- ggplot() + geom_point(data = data1, aes(x = clarity, y = m), color = "blue", size = 3) + geom_point(data = data2, aes(x = clarity, y = m), color = "red") #################################################### ############# Create and save widget ############### p <- ggplotly(gg) internalSaveWidget(p, 'out.html') ####################################################
Now you can compile the project: run pbiviz package from the folder pbiwidgets/diamonds.
Open Power BI and load the two Excel files. Select ‘Get more visuals’ as before (the icon with the three dots), ‘Import a visual from a file’ and select the pbiviz file located in the pbiwidgets/diamonds/dist folder.
Drag and drop the column names of the two datasets to put them in Data1 and Data2. Here is the result:
Helper function
Here is a R function which generates the capabilities.json file:
library(jsonlite) fill_capabilities <- function(path, dataNames, dataDisplayNames), dataNames, dataDisplayNames, SIMPLIFY = FALSE, USE.NAMES = FALSE) caps[["dataViewMappings"]][[ 1L ]][["scriptResult"]][["dataInput"]][["table"]][["rows"]][["select"]] <- lapply(dataNames, function(dataName)) caps <- toJSON(caps, auto_unbox = TRUE, null = "null", pretty = 2) writeLines(caps, "capabilities.json") }
Usage example:
fill_capabilities( "path/to/pbiwidgets/diamonds", c("data1", "data2"), c("Data1", "Data2") )
Limiting the number of selected columns
We can limit the number of columns that the user can select in a table. For instance, let’s take the previous example. The maximum number of columns is set in the file capabilities.json, in the dataViewMappings field:
, ], "dataViewMappings": [ , "data2": } ], "scriptResult": ], "objects": }, "ChartTheme": , , ] } } } } }, "dataViewMappings": [ from "powerbi-visuals-utils-dataviewutils"; import DataViewObjectsParser = dataViewObjectsParser.DataViewObjectsParser; export class VisualSettings extends DataViewObjectsParser export class ChartSettings
Then, in the file script.r the title set by the user and the theme he chooses are named ggsettings_ChartTitle and ggsettings_ChartTheme respectively. But they won’t be available at the first creation of the chart, so we have to check for their existence in the R script:
if(exists("ggsettings_ChartTheme") && exists("ggsettings_ChartTitle"))else if(exists("ggsettings_ChartTheme") && exists("ggsettings_ChartTitle"))else if(ggsettings_ChartTheme == "excel") } #################################################### ############# Create and save widget ############### p <- ggplotly(gg) internalSaveWidget(p, 'out.html') ####################################################
List of available object types
color picker:
"type": } }
text input:

Images Powered by Shutterstock