How to export an R dataframe to a Power Query table

Question

I'm using an R script within Power Query to do some data transformations and return a scaled table. My R code is like this:

# 'dataset' contém os dados de entrada neste script

library(dplyr)

df_normal <- log(dataset+1) %>%

select(c(2:4)) %>%

scale

df_normal <-cbind(dataset[,c(1)], df_normal)

output <- df_normal 

This works OK in R Studio and I'm getting the df_normal data frame as expected.

However, in Power Query, I get an empty table! How do I export the data frame to a table?

Answer 1

• While running an R Script in Power BI Desktop, there are few limitations:

• The data which we want to import to Power BI must be represented in a data frame.

• columns that are typed as complex and vector are not imported and are replaced with error values in the created table. 

These might be the most obvious reasons. Your dataset shows that the scale functions change your dataset into a matrix class object. This is done by cbind. The output is from the class matrix and not from data.frame.

>dataset <- as.data.frame(abs(matrix(rnorm(1000),ncol=4)))

>class(dataset)

[1]"data.frame"

>library(dplyr)

>df_normal <- log(dataset + 1) %>%

>    select(c(2:4)) %>%

>    scale 

>class(df_normal)

[1] "matrix"

>df_normal <- cbind(dataset[,1], df_normal)

>output <- df_normal

>class(output)

[1] "matrix"

By adding output <- as.data.frame(output), the issue can be fixed. This is the line with documentation of power BI. It might need a return statement at the end. we need to add a line at the end of the script simply stating output should fix this.

For more clarification, the following edited script of yours should return the data expected.

# 'dataset' contém os dados de entrada neste script

library(dplyr)

df_normal <- log(dataset+1) %>%

select(c(2:4)) %>%

scale

df_normal <-cbind(dataset[,c(1)], df_normal)

output <- as.data.frame(df_normal)

#output  ##This line might be needed without the first comment 

    To read the document about how to run R scripts in Power BI Desktop, refer to this link

https://docs.microsoft.com/en-us/power-bi/desktop-r-scripts