Power Query R script step not running when refresh in PowerBI Desktop

[jonsey1995]

Hi All,

I'm trying to export a query to excel using the the following R script step in Power Query:

install.packages("openxlsx")
library("openxlsx")
write.xlsx(dataset, "removed this but is a valid directory", asTable = TRUE, overwrite = TRUE)

When i refresh preview in power query, file is created/ overwritten correctly 👍

However when refreshing using powerBI Desktop the file is not created/overwritten 👎

Help appreciated, thanks!

[lbendlin · Accepted solution]

Seems to work fine  (within the limitations of this setup)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.Random()),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(""openxlsx"")#(lf)write.xlsx(dataset, ""C:\\users\\xxx\\downloads\\test.xlsx"", asTable = TRUE, overwrite = TRUE)#(lf)output<-dataset",[dataset=#"Added Custom"]),
    #"""output""" = #"Run R script"{[Name="output"]}[Value]
in
    #"""output"""

You don't really want to install packages in your script - do that in the R command line.