Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have an R-based algorithm that I am running in a query. The R script outputs a single dataframe and writes a timestamped .csv file. When I refresh this query in the Query Editor, I received one .csv output, as expected. But when I refresh my report in the report editor, I receive two .csv outputs with a ~6 second difference in timestamp. Running the algorithm twice makes the procedure much more resource-intensive for my computer, and it might cause my R script to time out at the 30 minute limit.
Are there any known fixes I can try? Is this a known issue?
Here is a similar issue which was not resolved:
R script runs query more than once
Here is a post by Ken Puls suggesting that Power BI might run queries once for preview and once for the actual load:
Power Query Refresh Speeds Suck
I was able to reproduce my problem in the simplest possible case. The issue seems to be the way I am using the parameter query, and that I reference this parameter when I define the dataset that feeds into my R script. This issue does not occur when there are no parameter references (i.e. if I run the R scipt and hard-code the output filepath, I get one set of outputs).
To test, you will need R Open installed. Then just create the following 2 queries via Advanced Editor, and name them as shown. Replace the path in "Folder Path to Write to:" with a location you prefer. Apply changes and return to the report editor. Each time you "Refresh" you will have 2 sets of csv outputs. The digits appended to the csv output names represent millisecond accuracy.
Folder Path to Write to:
"C:/Users/User_Name/Desktop" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
R_Script
let RScript = R.Execute("setwd(paste0(dataset$project_path))#(lf)
employee <- c('John Doe','Peter Gynn','Jolie Hope');#(lf)
salary <- c(21000, 23400, 26800)#(lf)
startdate <- as.Date(c('2010-11-1','2008-3-25','2007-3-14'))#(lf)
employ.data <- data.frame(employee, salary, startdate)#(lf)
write.table(employ.data, file = paste(""PBI_R_Output_Test_1_"", (as.numeric(format(Sys.time(), ""%OS3"")) * 1000), "".csv"", sep = """"), sep="","", row.names=FALSE, na = """")#(lf)
write.table(employ.data, file = paste(""PBI_R_Output_Test_2_"", (as.numeric(format(Sys.time(), ""%OS3"")) * 1000), "".csv"", sep = """"), sep="","", row.names=FALSE, na = """")",
[dataset=Table.FromList({[project_path=#"Folder Path to Write to:"]},Record.FieldValues,{"project_path"})]), #"employ data" = RScript{[Name="employ.data"]}[Value] in #"employ data"
Check if Option to disable data previews to download in the background helps.
Unfortunately I already had that setting disabled. Thanks for the input.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |