PowerBI offers some great native and custom visuals that integrate and interact almost perfect together.
But many times we need a visual that either doesn’t exist or doesn’t offer the functionality we want. There are great examples of R visuals with code online ( R community is also great ). If you are like me and you think the R script visual doesn’t look that polished ( resolution is not good ) or you need to interact with the R visual, then read below.
Also overcomes the limitation of “Publish to web’. The R custom html visual can be published to web!!
Disclaimer: I am not a developer and certainly an R coder, just happen to play around with ggplot2 and experiment with it ( a lot of googling ). Definitely are better ways to write both code.
If you are in above position and you know a bit of R and ggplot2 ( we are not going to discuss R code ) please welcome!!
Let’s build this R custom visual..
We will create a simple scatter plot on dummy entered data. Below is the table with data we are going to use.
a) You need to install some packages and other stuff to be able to design custom visuals for PowerBI. Please refer to this page for instructions.
***Make sure you have installed in R ggplot2 and plotly packages
c) It is easier to actually develop,debug and finalise the visual design in Rstudio and then create the R custom html visual.
Step 2: Design you visual in R ggplot2
On PowerBI desktop select the R visual (1) and drop the relevant columns (2). *Select ‘Don’t summarize’ as default aggregation of columns or use/ drop another column for grouping if you use measures. You can first create a table graph to inspect the results
Since this will be a simple scatter plot we drop ‘Price’ and ‘Quantity’. Automatically the relevant dataset will be created in R script editor. I use Rstudio to design & test the code as I found it easier. Click the arrow (3) to open Rstudio with dataset loaded. ( you will have to select your preferred R IDE on PowerBI desktop options )
We enter our R code for the ggplot2 visual and run to test if looks like we want.
When we finish with code and design ( we are happy with outcome ) then copy the code from Rstudio to R script editor in PowerBI and run the visual.
Copy the code starting from ‘library(…) — not above.
Now it works on PowerBI desktop also. Also interacts with when filtering ‘Product’ column.
Step 3: Creating R custom visual ( html )
Open Visual Studio code. Make terminal of VScode visible by selecting View->Terminal.
Go to terminal and navigate to a folder you want your custom visual to saved. I used a folder in my documents called ‘CustomVisuals’.
Pick a name for your visual and replace the bold text below with your text. Enter the command ‘ pbiviz new HowToRVisualHtml -t rhtml ’ in terminal and hit enter.
This action will create the R custom visual template and necessary components of R custom visual.
We can validate also that the folder is created.
Open the howToRVisual folder ( or the name you pick ) from VS code , File -> Open Folder.
Now we see all components of the custom visual.
While on VScode open/select ‘script.r’ file and copy the code from PowerBI R script editor under the “Actual code” line.
Note that (1) you need to replace ‘dataset’ with ‘Values’, (2) add a semicolon at the end of code, (3) replace ‘->’ from R with an equal sign ‘=’ , (4) copy the R graph variable name you use in R code add it below in ggplotly function
Navigate to ‘src’ folder -> ‘visual.ts’ file.
Replace ‘NodeListOf’ with ‘HTMLCollectionOf’. Don’t ask me why, I don’t know :)
Last, go to ‘pbiviz.json’ and make sure to enter your name and email, support url, and a description for the visual. It will not work otherwise.
We are almost ready to create our R custom visual. Exciting!!
Go to terminal and enter command — “pbiviz package” and hit enter. Watch the terminal compile the visual.
If we don’t see any errors, means we made it.
Ready? Go to PowerBI desktop and import a custom visual from a file. The custom visual is located in the custom visual folder under the ‘dist’ folder.
Done!!! Replace the previous R visual with the new added R custom visual
You can “Publish to web” or PowerBI service and it will work. I think this is great.
Check the report here
Ending, I will like to mention the that above R custom visual is ‘hardcoded’. That means column names and other variables like color and size are fixed on the custom visual code. Columns names in your dataset should match the ones in R visual ( script.r ) to work.
It is possible to add variable name in columns and formatting pane in R custom visual code but I found it too difficult and time consuming as I mostly design an one time or purpose R visual. Plus I don’t know how to code them with latest visual API updates :)
In order to make changes to R custom visual just change ‘script.r’ file and package the visual again with VScode. Importing custom visual from file in PowerBI desktop will update the current version.
Hope you enjoy and create exciting R custom visuals to publish in web!!
** Above are working with current version of PowerBI ( Nov 2019 ), if Microsoft changes the html R visual template I cannot qurantee it will work.