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
Super UserHi 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
Hi BA_Pete,
Thank you for taking the time to reply to me.
If Power Query is not the best tool for this, do you have any other suggestions how I could go about this in Excel? Essentially taking a report, making a copy and then downloading the report the following day, merging the new report with the copy, and then removing duplicates. So I am creating a rolling report day by day....?
Thanks, Lee
- BA_Pete
It depends on what quantity of data we're talking about.
The simplest solution would probably be to save every source file into a SharePoint or network folder then connect to that using either the 'SharePoint Folder' or 'Folder' connector, then add a Remove Duplicates step into the Transform Sample File query. However, if we're talking about hundreds of thousands of rows and 50-odd columns in every file (and growing) then this may not be feasible (or just incredibly slow and/or resource-intensive).
If the files are actually individually very large, then I think you'll want to look into a proper data warehousing solution.
Pete