I take some data from Redshift through some Python scripts. I have set my scripts and I get 9 tables. Then I merge them into a new table that I call "Current Snapshot".
The data refer to how many blank and null values I have in 9 different systems on a given point in time. I want that next month when I refresh the 9 tables and produce the new version of "Current Snapshot" the old data need to stay and added to the new.
In order to achieve this goal, I create an empty table that I call "Overall Snapshot". I append the records present in "Current Snapshot" to this empty table. In my mind next month I refresh Current Snapshot and add these new data into "Overall Snapshot" that kept the old data. The problem is that as soon that Current Snapshot refreshes, automatically "Overall Snapshot" does the same. In M unfortunately there is "copy as value" function that we have in Excel.
I cannot create a sort of function that passes a date. It's just like a snapshot of the system at a given point in time that I would like to keep off line and when I go to take the new snapshot I want to merge it with the old one. I looked into the incremental load but I do not think this solves my problem. In this situation "Overall Snapshot" should grow with time and the Current Snapshot have to contain the most recent values.
Does it make sense what I am asking? Any ideas to solve my problem?
Thinking better to what you suggested, even in my scenario of continuous refresh, I need to create 1 new table for copy that I want to hold. So when week 3 comes I will duplicate the previous snapshot of week 2, set to not refresh, close Power Query and then refresh the current snapshot. When time passes I will have more and more tables and I will increment the union with one more table. In theory should work.
Thank you very much. As soon as I verify that it works I will accept your solution. Things here are really slow.