Forum Discussion
Export data from All visuals at once
Hi all,
A client requested to have a csv export of the data for each visual in the report. The report has many visuals and it updates every 30min.
I'm looking into a way to automate this export so that they can have quick access to constantly updated CSVs (or possibly an excel which includes all exports in separate sheets), to avoid the manual labour needed (three dots, export data etc for each) since it's time-consuming for the client.
Does anyone know if this is possible in PowerBI, maybe using some DAX Studio or R or Python script?
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!
11 Replies
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,
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.
Hi Anonymous ,
Isn't the function Analyze in Excel an option ?
https://docs.microsoft.com/en-us/power-bi/service-analyze-in-excel
Hi Anonymous ,
Dax Studio can run the query of visual into a csv file but we did not find a way to run it automatically.
Or you can try to use the XMLA endpoint solution mentioned in this thread if you're on premium
Best regards,Where do you write and run the script that trigger this functionality? 🙂
Im also trying to find a workaround for exporting Visuals data from my PBI reports.
My goal is to be able to export PBI reports Visual data on the server side, via an api request .
Basically I want to achieve the same functionality of the PBI embedded JavaScript SKD function here: https://github.com/microsoft/PowerBI-JavaScript/wiki/Export-Data
Limitation at the monument is that i can do this only on the client side, That mean that every time I want to export data from my visuals i must first render the report on the browser and only after the embedded report has been rendered, i can use the JS SDK visual.exportData() function . I'm trying to understand if there is any way to achieve this on the server side or as an automatic service that can run in PowerBi.
Thank you! 🙂
