Setting Up R in Power BI

To draw an R chart in Power BI, the first step is to install at least one R version or Microsoft R Open. Now we must specify what R version we are going to use. To do this, we click the File menu, then click Options and Settings, then Options. Under the Global option, click R Scripting, to specify the R version (Figure 4-3).

As mentioned in Chapter 3, R is based on packages. Whenever you use a specific package, you must install it on your machine first. (If you are using Power BI Desktop, most of the packages are already installed in the service.) You can install packages using an R IDE, e.g., R Studio. Then, in the Power BI R editor, you must refer to that package. Moreover, you ensure that you are using the right version of the R package that you already installed (Figure 4-4).

Writing R Code in Power BI

It is possible to write R code in Power BI’s report area. To do that, we must import data into Power BI report.

A data set that includes car specifications, such as speed in town and along a highway, cylinders, and so forth, is available to download at https://forge.scilab.org/index.php/p/rdataset/source/tree/master/csv/ggplot2/mpg.csv . This data set is free and titled “mpg.csv.” I am going to use this data set to show the speed of the car on the highway and the city, the number of cylinders the car has, its year of production, and type of drive (front wheel, rear wheel, and so on) in one picture. According to Figure 4-1, to show the comparison of the data among the different items, we must use a table chart. We are going to import data into Power BI Desktop via the Get Data option and import a CSV file (Figure 4-5).

After importing the data, you should be able to see it under Fields. Click the R and drag it into the whitespace area (Figure 4-6).

Expand the mpg data set (Figure 4-7) and choose cty (speed in the city), hwy (speed on the highway), and cyl (cylinder) options.

The R scripts editor will be enabled. Under R script editor, you will see some R codes. The crosshatch (#) is a symbol for making comments in R and is not executed as script. Power BI automatically puts the selected fields in a variable named dataset, so all fields (cty, hwy, and cyl) will be stored in a data set variable equivalent to using the <- syntax. Also, Power BI automatically removes any duplicated rows. This is explained in the R script editor area (Figure 4-8).

Next, we are going to put R codes for drawing a two-dimensional graph in Power BI. In Power BI, to use any R scripts, install the package in R, using the install.package() function. In our case, we must install ggplot2. We use the function library(ggplot2) to refer to this package. The first line of code in the R editor is library(ggplot2). This package contains some important functions for drawing charts.

To draw a chart, I first use the ggplot function, to configure a two-dimensional chart. The first argument is dataset, which holds our three fields. Then we have another function inside ggplot2, named aes, which identifies which file should be on the x or y axes. Finally, in the chart, I also want to indicate the number of the car’s cylinders. This can be done by adding another layer in the aes function: Size. So, cars with more cylinders will have bigger dots (see Figure 4-9).

t←ggplot(dataset, aes(x=cty, y=hwy,size=cyl))

However, the preceding figure shows the graph area without any specific charts. We need a scatter chart to indicate the city and highway speeds on x and y axes. ggplot2’s geom_point function draws a scatter chart. This function has value of pch=21, which relates to the shape of the dot in the chart. For example, if I set this value as 20, it becomes a filled cycle; a value of 23 becomes a diamond shape. To run the code, click the run option (Figure 4-10).

After clicking the run code option, the charts will be shown in R visual (Figure 4-11).

The difference between the lowest and highest value specified is 5. I can increase this difference by changing it from 5 to 10. The resulting scatter chart is shown in Figure 4-12.

In the next example, I have changed the pch value to 24 and added another code inside the aes function name: fill=Red. This means that the chart will show solid red rectangles (Figure 4-13).

t<-ggplot(dataset, aes(x=cty, y=hwy,size=cyl,fill="Red")) + geom_point(pch=24)+scale_size_continuous(range=c(1,5))

By changing the aes function argument, we have replaced size argument with color. This indicates that I want to differentiate a car’s cylinder values not just by cycle size, but that I am going to show them by allocating different colors to them. Hence, I change the aes function as in the preceding code snippet.

Now, you can see the car’s speed on the highway and in the city in y and x axes. Also, we have cylinders as a color and drive and year of production as a facet (Figure 4-14).

Slice and Dice

R visuals are interactive. It is possible to slice and dice them. We are going to use a slicer to slice the facet chart. We also are going to insert a year slicer in the report area, to filter the R visual (Figure 4-15).

Edit R Code in RStudio

It is possible to open the code in RStudio with the data. There is also an option to run the R code in RStudio using the data we have (Figure 4-16).

After clicking the Run option, a page will be open in RStudio that contains the data related to the car. The imported data set contains all changes in data (Figure 4-17).

Custom Visuals in the Power BI Office Store

The Office store provides a variety of custom visuals to the Power BI user. To access them, you must first sign in to your work account (Figure 4-18).

Then, in the Power BI Visualizations panel, click the three dots and select the Import from store option (Figure 4-19).

Next, you will see a page with the title “Power BI Custom Visuals.” There are many categories listed here, such as filters, KPIs, Maps, Advanced Analytics, Time, Gauges, Infographics, and Data Visualizations (Figure 4-20).

As mentioned, Power BI Custom Visuals includes an Advanced Analytics category. By clicking it, you will see such advanced analytics as time series, associative rules, clustering, and more. I am going to use one of these custom visuals to forecast milk production.

The first step is to import a milk data set into Power BI Desktop. The data has two columns: date of milk production and value of the milk (Figure 4-21).

We must now import custom visuals from Power BI Custom Visuals, to forecast milk production (Figure 4-22).

After importing Custom Visuals, you will see that it has been added to the standard visual panel (Figure 4-23).

Having imported the forecasting custom visual, we must now choose the date and value for the data field.

This custom visual uses an Exponential Smoothing algorithm for forecasting milk production over the next ten months (Figure 4-24).

The forecasting chart shows in yellow the actual data related to the production of milk in the previous months, and the forecasting for the later ten months is in red.

The algorithm behind the scenes is not accessible, and we are only able to change the parameters (Figure 4-25).

Custom visuals in Power BI extend the possibility of having different charts. However, the number of custom visuals created by the Microsoft team is limited. To extend visualization capabilities, there is a way to create custom visuals by using R scripts. In the next section, I will explain the process of creating custom visuals.

Creating Custom Visuals

Some of the main Power BI standard visuals in Power BI Desktop are widely used. However, as mentioned in the previous section, it is possible to extend these with custom visuals. To access the custom visuals, you must click Market Place in Power BI Desktop. There are two ways to create custom visuals using Java scripts or R codes.

npm install -g powerbi-visuals-tools

By running the code, a folder with the name sampleRHTMLVisual will be created in CustomVisual folder. In this folder, there is an R file with the name script.r (Figure 4-28).

This is a folder that provides a template with which to create other R custom visuals. Check the file script. R inside the folder (Figure 4-29).

Now, as you can see in the code in Figure 4-29, we require two libraries, Plotly and ggplot2, to draw a simple ggplot2 chart with Plotly.

Here, the data set has been hard-coded for iris, which is an open source data set in R. The plot gets the data from the iris data set and shows the petal and the length and species of the flower. Then we use the ggplotly function to show the data.

As a result, we have an R script. Now I first will create a package from this, then I will write my codes to create different charts. Hence, I return to the command prompt and type pbiviz package in the folder (Figure 4-30).

We can change the R code and create a custom visual with a different name and icon. In another example, I am going to create a table chart. (I already explained the R codes for this earlier in the chapter.)

The main data set is Values. The first item in the Values data set relates to the x axis, the second to the y axis, and so forth.

So, in Power BI Desktop, be careful about the axis you specify in the R code.

As you can see in Figure 4-37, the imported visual has a name and icon. In addition, in the data fields, we have Values as the main data set, and the sequence of the variable we put there is as in the code we specify for each of them. For example, the cty field will be located on the x axis, hwy on the y axis (the second value), and so on.

It is possible to change the Value fields to have separate x, y, legend, row, and column data fields. This can be done from capabilities.JSON. Additional information on how to change the data fields and how to add more settings is accessible via other Microsoft sources and the RADACAD web site .