Forum Discussion
Get data from source, save that data to a table each day, retain records that have been deleted
Hi Lee,
Power Query is not a data warehouse and is not designed to retain data beyond its immediate use.
The PQ refresh process works like this:
Wipe all data > Import available data from source > Perform transformations > Push to Data Model.
As such, there's no function to create and retain history/archives of changing source data.
The closest native function (within the Power BI universe) would be Incremental Refresh, which comes with its own significant caveats:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
Beyond this, you will need to arrange some kind of data warehousing or storage capability at your end to keep the history that you can then query with PQ.
Pete
Most Valuable ProfessionalIf you're trying to retain a history for the file in a way that's accessible to PBI, you could use Power Automate on a schedule to copy it to a new file and use the date in the filename to give it uniqueness. If you use the folder connector then, I guess you could get all versions that ever existed... it might be a good idea to have Power Automate set a "snapshot date" field in association with the file copy, or extract it from the filename qhen you're querying, so that you have some idea of when the snapshots are from.
It sounds kind of like you could use a real records management system here, though, vs trying to manage this in Excel. Dynamics or even a basic SharePoint list would be better - at least in a SP list you can trigger actions based on things happening to the record, such as status changing to paid, that give you an indication of what's changed when.