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.