Maintain historical Data

[sonm10]

Hello all,

I have a transaction system that contains percentage completion and status of Projects. The thing is, when the percentage completion of a project is updated, the previous value is lost and hence,the progress of project over time cannot be determined. I need to show the progress of the project over time and hence wants to append the data on refresh instead of updating. How can I acheive this? I am refreshing the data daily.

* Modifying the source system is not an option.

[pthapa]

Incremental refresh might be an option if you want to have the hisorical data not changed but only change the specific number of days data in PBI.

Check out some incremental refresh videos on youtube, might be helpful.

Thanks,

pthapa

[Anonymous]

If you are using power BI and not just Excel, you can check out this video which will explain how to populate a history table using incremental refresh. If you can modify the Query a bit, this would do the trick!

https://www.youtube.com/watch?v=aAzSZbrICxE

[edhans · Accepted solution]

You cannot. Power BI is a "read only" system so it doesn't have the ability to store data beyond a refresh. 

You will either have to modify the source system (you said not an option), or put in an intermediate process in place to store a previous day snapshot of data, then have Power BI read in the source data and the snapshot, then do the comparison.

[sonm10]

Thanks for your response. How do I put in the intermediate process.

[edhans]

---

@sonm10 wrote:

Thanks for your response. How do I put in the intermediate process.

---

It really depends on your data. A few things to think about:

1. If your data is file based (XLSX, CSV, etc) just make a backup copy of each file and stick it in another folder. Then user Power BI to read that file in as "yesterday" data to compare to today data.
2. If your data is from a database, like SQL Server, and you are unable to get a dba to create a rolling snapshot for you (a table with yesterday's data for example) you could do it with Excel. Just use Power Query in Excel to connect to the same data source. Read in that data and load it to the spreadsheet. This assumes it is under 1M rows as that is Excel's limit. Then use that as your "yesterday" data.
3. If you can export your data from the database as a CSV, do that for yesterday.
4. If all of that fails, can you connect MIcrosoft Access to it? Access can read the data and export it to a CSV for you. You need to know a little about Access to do this, but you do not need to know complex things like VBA, forms, or even macros.

Other than that, you may need outside assistance. If you are good with Power Automate, you may be able to automate a lot of this, like making copies of the files if they are file based, or even launching the Excel file, running a macro to refresh the table, saving and exiting, can be scheduled with the Windows 10 task scheduler.