Forum Discussion
Export data from All visuals at once
Thanks for the tips guys,
They seem all good workarounds and they led me to consider this option:
- I create new queries in PowerBI query editor, to match the queries outputted by the visuals (using GroupBy and calculations needed).
- I write a script in R to export them to .csv to a dedicated folder. At every scheduled refresh the R code runs and updates the files (that I name with each visuals' title) showing the latest data.
It seems to work for now, and it's fully automated. I'll keep you posted in case errors arise or performance goes down the drain using this method,
Thanks!
Thanks for the tips guys,
They seem all good workarounds and they led me to consider this option:
- I create new queries in PowerBI query editor, to match the queries outputted by the visuals (using GroupBy and calculations needed).
- I write a script in R to export them to .csv to a dedicated folder. At every scheduled refresh the R code runs and updates the files (that I name with each visuals' title) showing the latest data.
It seems to work for now, and it's fully automated. I'll keep you posted in case errors arise or performance goes down the drain using this method,
Thanks!
Hi Anonymous ,
Glad to hear you make a solution that transform the DAX query of visual to the M query and use a R script to export the data, it sounds great, thank you for sharing it.
We think currently the biggest problem seems some measure is difficult to transform into M query and please notice that when you reference another query in power query editor, it may query the source multi times, please refer to this documnet about it:
Best regards,
Thanks for the tip! will probably use dataflows then
Anonymous is this published on the service? If so where are you writing your csv files to? If not, how is it fully automated?
Regards
Pat
Hi mahoneypat ,
The first step is to create a dedicated query where you can replicate the Visual's table (to see it, three dots -> show as table). This can vary in complexity depending on your visuals.
To export to csv I added R code to each of these queries:
write.table(dataset, file = " folder_path/MyVisualTitle.csv", sep="\t", dec=".", row.names = FALSE)
The files are saved into a shared folder in OneDrive, in my case.
The automation is in the fact that every time the scheduled refresh runs, also the R code runs. The files are overwritten at every refresh, so that the latest update is always available.
Thanks Anonymous for the response. I am familiar with making query tables and write them to a local drive with R. I wasn't aware you could write to a OneDrive folder in the cloud. Since you mention automatic refresh, I just want to confirm that yours is running on the Power BI service and writing to OneDrive from the cloud? If so, can you let me know the path you use to in write.csv (more details on "folder_path" below)? Also, are you using OneDrive for Business or Personal?
Regards,
Pat
