.png?width=165&name=Untitled%20design(66).png "Untitled design(66)")
R custom visuals for Power BI allow developers to create and package new visuals with only a few lines of R code. While previously limited to creating static output, Microsoft now allows for interactive HTML content. This change opens up the possibility to render rich content using libraries such as htmlwidgets, Plotly, and more.
This tutorial steps through the process of adding a sample R HTML visual, viewing it in Power BI Service, and altering the R script to quickly change the appearance of the custom visual.
In this first exercise, you will create a new R visual project using the Power BI Command Line Interface (CLI). Microsoft provides a template for interactive R visuals that populates the project with a sample script and an interactive ggplotly visual.
1. Verify that you have completed the prerequisites listed above to install and use the Power BI Custom Visuals Tools Command Line Interface (CLI) with Node.js.
2. Open the Node.js Command Prompt.
3. Enter and execute the following command:
pbiviz new sampleR -t rhtml
The Power BI visual tools (pbiviz) creates a new interactive R visual (rhtml) with the name provided (sampleR). The new option creates the visual.
4. Navigate into the new folder created for your visual, and then load the visual using the start command:
cd sampleR
pbiviz start
Starting the visual makes it available to Power BI Service. Any change made to a project file while the visual server is running rebuilds the visual, and you can view these changes after a quick visual refresh in Power BI Service.
In addition, the current directory contains all of the files associated with your Power BI visual project. On the surface, almost everything looks the same as a standard Power BI custom visual with the exception of the r_files directory and script.r file. The content in r_files is used to make the output interactive HTML instead of a static image as in a normal R visual. You will spend all of your time editing script.r for this tutorial. In the future, you could extend your visual with format options and more by enabling additional capabilities and editing the main visual.ts file. For now, however, let’s stick to R!
1. Download the sample CSV file used in this tutorial: HR_comma_sep.csv.
2. In a browser, login to the Power BI Service by going to http://www.powerbi.com and selecting Sign In. Enter your account information.
3. Select Get Data.
4. Under the Files section, select Get.
5. Choose Local File.
6. Browse to the HR_comma_sep.csv file previously downloaded in step 1 and open it in Power BI.
7. Under your workspace, browse Datasets and find the newly added HR_comma_sep dataset.
8. Click the bar chart icon to add a new report that uses this dataset.
You should see fields from the HR_comma_sep dataset and an empty report canvas
1. Select the Developer Visual in the Visualizations pane. NOTE: If you do not see the Developer Visual in the bottom row, verify that you have follow all of the prerequisites for this tutorial.
2. Add the sales, salary, and satisfaction_level fields to Values for the Developer Visual.
3. Verify that satisfaction_level aggregates as a Sum instead of being set to Don't Summarize by clicking on the arrow next to that field. Change it to Sum if necessary.
Even though you added data from the sample HR dataset, the visual should display a bar chart that uses the iris dataset. This is because Microsoft's sample visual is hardcoded to the iris sample dataset. In the next exercise, you will change the R script to incorporate data from your HR dataset.
1. Browse to the sampleR folder using File Explorer. Recall that this folder was created when you added the new R visual using the Node.js command prompt.
2. Open the script.r file using your preferred text editor.
3. Review the R code contained in Microsoft's template visual.
4. Replace Line 9 with the following code:
g = qplot(`sales`, data = Values);
This will alter the existing plot to use the Values data frame from the Power BI dataset and plot the count of sales. This is meant to illustrate how to easily change the code and will not be a useful visual.
5. Save the changes to the script.r file.
6. Go to the report in Power BI Service and click the refresh arrow for the Developer Visual. After refreshing, you should see the visual updated to include data from the HR dataset.
The previous example modified the template and combined the ggplot2 and Plotly libraries to turn a static plot into an interactive one. You can also use Plotly directly without ggplot2.
1. Replace the entire contents of script.r with the following sample and save the file:
source('./r_files/flatten_HTML.r')
libraryRequireInstall("plotly")
p = plot_ly(Values,
x = Values$sales,
y = Values$satisfaction_level
)
internalSaveWidget(p, 'out.html');
2. Go to the report in Power BI Service and click the refresh arrow for the Developer Visual. After refreshing, you should see the visual updated to include a basic Plotly bar chart.
In addition to basic bar charts, you can create more advanced types of plots using Plotly. In this exercise, you will add code to create a heatmap that colors the intersections of sales and salary values with the intensity of satisfaction_level.
1. Replace the entire contents of script.r with the following sample and save the file:
source('./r_files/flatten_HTML.r')
libraryRequireInstall("plotly")
p = plot_ly(Values,
x = Values$sales,
y = Values$salary,
z = Values$satisfaction_level,
type = "heatmap"
)
internalSaveWidget(p, 'out.html');
2. Go to the report in Power BI Service and click the refresh arrow for the Developer Visual. After refreshing, you should see the visual updated to include the Plotly heatmap.
In addition to Plotly, many other interactive R packages are available in Power BI Service. DT is a library that adds interactive tables using only a few lines of R code.
1. Replace the entire contents of script.r with the following sample and save the file:
source('./r_files/flatten_HTML.r')
libraryRequireInstall("DT")
p = datatable(Values, width = "100%")
internalSaveWidget(p, 'out.html');
2. Go to the report in Power BI Service and click the refresh arrow for the Developer Visual. After refreshing, you should see the visual updated to include the DT table. You may need to increase the height and width of the visual to view the entire table.
