Forum Discussion

sonm10's avatar
sonm10
Helper I
6 years ago
Solved

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 an...
Show and Tell
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    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.

edhans's avatar
edhans
Community Champion
6 years ago

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's avatar
    sonm10
    Helper I
    6 years ago

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

    • edhans's avatar
      edhans
      Community Champion
      6 years ago
      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.