I would like to store historical data from my Power BI reports in order to keep track of some KPI’s, to use analyze their evolution from a historical perspective.
My dataset consists of some tables from our Dynamics CRM (Odata source), and three tables on an excel file. At the moment, the best thing I’ve been able to get, is a CSV export using top right menu in Power BI charts/tables, or an R script that I have to execute manually every day, week or month depending on the periodicity of my historic logs.
The fact that this is a manual process and that I want to extract the historical data from several reports makes this process impractical, so I’d like to know if there’s a way to automatize it. Thinking about it we see only three possibilities:
I don’t know if the third option is even viable. How do I connect to Power BI’s internal database (online ideally vs desktop)? Could somebody throw some light into this matter? Any comments on options 1 and 2 or any other potential solutions would also be appreciated? Has somebody cracked the historic log problem with Power BI?
Solved! Go to Solution.
My solution in this post 😄 http://community.powerbi.com/t5/Desktop/Storing-and-using-information-from-a-dynamic-data-source-usi...
The script file for task scheduler is not found on your onedrive.
Can we a have a different solution for this?
Please let me know. It's urgently required. Thank you.
Looks like exactly what i'm looking for, but I'm a real PowerShell numptie so going to take a while to understand. Can I just check your script is design to out putt he whole of a table in Power BI Desktop including any calcuated columns?
Is there any way to use the same approach to export a specific visual that contains measures rather than column values? For example i have a clauation that works out the totla vaue of outstanding quotes, but want to create a time series of this calculation.
I'm curious what R script you used to store and display historical data. My data's status fields change over time, and it'd be really useful if Power BI took a snapshot of the data periodically and stored that snapshot, in order to plot trends, etc. It sounds like you have that solution in hand using R. Can you point me to the resources you used to construct the R script?
I ended using Powershell and the task sheuduler to solve this issue, it runs power bi desktop every night to save csv files from some tables I want to track, and then re-feeds them into my dataset.
I'm working on a guide on how to do this, since I was able to set it up thanks to the Power BI community, and will publish it here when it's ready (+/- 2 weeks)
Ah, v-yuezhe-msft posted some links above, I just didn't spot them. I'll give them a look!
Firstly, an idea about automatically exporting data on different time basis has been submitted in this link, please vote it up.
Secondly, to some extent, you can use R script to export data to CSV when updating your dataset, please check alanhodgson’s reply about this method in this thread.
Thirdly, I am afraid that you are not able to connect to Power BI internal database, Power BI manages the storage of data and metadata using Azure BLOB and Azure SQL Database in a secured way, please review this article to get more details.
Thanks Lydia, voted, I wish that they implement that soon.
Imke from Thebiccountant.com has given me a solution that seems to fit my needs, I still need to put it in practice 😄
other solutions that I’m aware of is:
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.