Export data from All visuals at once

[Anonymous]

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!

[ybd]

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! 🙂

[Anonymous · Accepted solution]

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!

[mahoneypat]

@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

[Anonymous]

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.

[mahoneypat]

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

[dem167]

Good afternoon Pat; I'm looking for a similar solution, I have a multi entity report in PBI which has a tab with a visual for each entity. Can someone help a non program hack this? thanks

[Anonymous]

Hi @mahoneypat , 

yes the report is published on PBI service, the csv are created in a folder in OneDrive Business. The path comes from the OneDrive desktop app, since in this way you can find the "C:/" path of the folder and avoid the url problem

[v-lid-msft]

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: 

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries

Best regards,

[Anonymous]

Thanks for the tip! will probably use dataflows then

[v-lid-msft]

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,

[camargos88]

Hi @Anonymous ,

Isn't the function Analyze in Excel an option ? 

https://docs.microsoft.com/en-us/power-bi/service-analyze-in-excel