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' 

Answer 1

It does seem like odd that this fails to return. A quick glance online gave this 3 minute youtube video, which uses the same method, which you are using. Further searching down a source, one may come across the Microsoft Documentation, which gives a possible reason for why there might be an issue.

When preparing and running an R script in Power BI Desktop, there are a few limitations:

• Only data frames are imported, so make sure the data you want to import to Power BI is 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 seem like the most obvious reasons. Betting that there is no complex columns in your dataset, I'd believe the prior is likely the reason. A quick recreation of your dataset shows that the scale functions changes your dataset into a matrix class object. This is kept by cbind, and as such output is of class matrix and not 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"

A simple fix would then seem to be adding output <- as.data.frame(output), as this is in line with the documentation of powerBI. Maybe it would need a return like statement at the end. Adding a line at the end of the script simply stating output should fix this.

Edit

For clarification, I believe 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