The R Script transform

With the R distribution now installed and configured to integrate with Power BI, you are now ready to see what's possible with these new capabilities. In this example, you will be looking at data from the European Stock Market. The problem with this dataset, that must be corrected with R, is that the file provided to you is missing values for certain days. So, to get a more accurate reading of the stock market, you will use an R package called MICE to impute the missing values:

1. Before beginning in Power BI you should ensure that the MICE library is installed and available in the R Distribute you installed in the last section. To do this, launch Microsoft R Open from your device. This is the basic RGui that was installed for you to run R scripts with. Microsoft R Open may need to be run as administrator. To do this right-click on the application and select Run as administrator.

1. Type the following script in the R Console window, and then hit Enter:

         install.packages("mice")

1. You can close the R Console and return to the Power BI Desktop after it returns back package 'mice' successfully unpacked and MD5 sums checked. 
2. In the Power BI Desktop, start by connecting to the required csv data source called EuStockMarkets_NA.csv from the book source files. Once you connect to the file, click Edit to launch the Power Query Editor.
3. You will notice that there are a few days that are missing a SMI (Stock Market Index) value. The values that show NA we would like to replace using an R script. Go under the Transform ribbon, and select the Run R Script button on the far right.
4. Use the following R script to call the MICE library that you recently installed to detect what the missing values in this dataset should be:

      # 'dataset' holds the input data for this script
      library(mice)
      tempData <- mice(dataset,m=1,maxit=50,meth='pmm',seed=100)

      completedData <- complete(tempData,1)
      output <- dataset
      output$completedValues <- completedData$"SMI missing values"

1. Click OK, and then click on the hyperlink for the table next to the completedData row to see the result of the newly implemented transform for detecting missing values.

This new output has replaced the missing values with new values that were detected based on the algorithm used within the R script. To now build a set of report visuals on this example, you can click Close & Apply under the Home ribbon.

This is just one simple way that R can be used with Power BI. You should note that in addition to using R as a transform, it can also be used as a data source and as a visual within Power BI.