Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
oslosa
Helper I
Helper I

Refresh not running R script

Hi, I have data that is updated every day, but I want to store historical values so that they are not overwritten. The way I am trying to do this is to export my dataset and append it to a csv file, with an added column that inidcates the time of the extraction. This works when I hit "refresh preview" in the query editor. However, when I hit refresh in the regular desktop view it seems to be skipping the R script steps as the csv is not modified in any way. Is there some setting I am missing?

Also, has anyone tried this with scheduled refresh?

5 REPLIES 5
jonsey1995
Frequent Visitor

Also having this issue, has a solution been found?

 

R query i'm using 

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

v-lid-msft
Community Support
Community Support

Hi @oslosa ,

 

 

Sorry for that, but we cannot reproduce this issue on my side with following steps, it export a csv file successfully by using refresh in the Power Query and desktop view.

 

Here are the steps,

  1. enter a simple table in Power Query.
 

38.png

 

  1. Then we run the R script to export csv file,
df <- data.frame(name = c("Jon", "Bill", "Maria"),age = c(23, 41, 32))
write.csv(df,"D:\\MyData.csv", row.names = FALSE)
 

39.png

 

  1. In the Power Query, we test refresh.
 

40.png

 

And we test refresh in the Desktop view,

 

 

Could you please try to reproduce this issue with some simple  R Script and the latest version Power BI Desktop?

 

Here are the queries we used, for reference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPU9JRMjJWitWJVnLKzMkB8kwMwTzfxKLMRCDX2EgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, age = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"age", Int64.Type}}),
    #"Run R script1" = R.Execute("df <- data.frame(name = c(""Jon"", ""Bill"", ""Maria""),age = c(23, 41, 32))#(lf)write.csv(df,""D:\\MyData.csv"", row.names = FALSE)",[dataset=#"Changed Type"]),
    #"df1" = #"Run R script1"{[Name="df"]}[Value]
in
    #"df1"

 


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft , I tested your code and it works. It also works when I modify from write.csv to write.table and add append=TRUE, which is what I have used on my data. It seems to be refreshing and updating the csv both in query editor, desktop view and via scheduled refresh, although creating some duplicates.

 

For my dataset (which is only 130kb) I experience the following:

1. The csv file is updated when I refresh from the query editor

2. The csv file is updated every time I open the .pbix file (which is surprising and does not happen with your example)

3. The csv file is not updated when I refresh from desktop

4. The csv file is not updated when I run scheduled refresh in the online version.

 

Any ideas? It seems that the refreshes are stored as backlogs until I close and open the pbix file.

 

For reference I am running this:

# 'dataset' holds the input data for this script
df <- data.frame(dataset)
write.table(df,"H:/Power BI/ProdData.csv",sep = ",", col.names = !file.exists("H:/Power BI/ProdData.csv"), append = TRUE)

 

where the data source is a 130kb table from SAP Business Warehouse.

I am also having this exact same issue. 

Having exact same issue. Would appreciate a explanation and fix.

 

J

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors