Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

     

     

     

     

      

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
mahoneypat
Microsoft Employee
6 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • dem167's avatar
    dem167
    New Member
    3 years ago

    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