February 12, 2021
Running External R Scripts in Power BI
For Power BI version 2.76.5678.782 (December 2019).
Writing R script in Power BI can be an uneasy task, since there is no variable explorer. That’s why we usually write our script in R and then import them into Power BI. How? We save the script as R file and import it in Power BI using source() function. Let’s look at a simple example.
Example: column statistics
We have a following table in Power Query.
We would like to print out statistical properties of both column, such as average, minimum, maximum, etc. We use R function summary(). Function returns average, median, minimum, maximum, first and third quarter.
We follow the steps bellow.
Open new R script in RStudio.
Enter following commands.
df = do.call(cbind, lapply(dataset, summary)) #summary function, but in dataframe form
df = cbind(rownames(df), data.frame(df, row.names=NULL)) #index to column
names(df)[names(df) == “rownames(df)”] <- “Statistics” #rename index column
Simple summary(dataset) that returns a table will now return a dataframe. We always have to end with dataframe result, since this is the only object Power Bi recognizes.
Save the script to a any location, for example H:\PowerBI\Running_R_From_PowerBI.
Select the table in Power Query and open R editor with Run R script.
Enter the following command.
source(“H:\\ PowerBI\\Running_R_From_PowerBI\\R_Script.R”)
Confirm with OK.
We got a table with statistics of both columns.
Every time the table dataset refreshes R script is run and result is updated. If refreshed table contains different or new value the statistics table will change accordingly.