Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sonm10
Helper I
Helper I

Maintain historical Data

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.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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


@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors