Every day I load the current data from an Oracle database into PowerBI via SQL query. This table is then called tabLoad
, which always contains the same columns, although there is also the valuationtime
column in the form DD.MM.YYYY
, which indicates today's/current key date.
This source query happens daily and only contains the current data from today. So I can currently only present the report as a daily report.
In the future, however, I would like every report user who views the report to also have the choice to look at past days, for example what the data looked like a week ago. However, for this I need a historicization of my data based on the valuationtime
column.
Is there somehow a way in PowerQuery to save the data from the tabLoad
table into a tabHisto
table every day after it has been successfully loaded from the database. This means that every day this tabHisto
would grow by an additional day, row by row.
The tabLoad
table looks like this - with data for today (07.05.2024):
Tomorrow the table structure will look the same, only the values will probably change, the valuationtime
will then definitely be 08.05.2024.
Simple answer is no I'm afraid. PowerBI doesn't store historic data and re-streams from the source each time. If you want historic data to be displayed, you need to store that somewhere outside of Power BI to ingest.
Why don't you store it in your Oracle database and consider using incremental refresh if the data size is very large?
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview