Advanced Incremental Load

[Anonymous]

Hi,

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?

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Per your scenario, 

then open the Edit Queries, copy table to get a new table (table1->table2)

Right-click on the Table1, uncheck the "Include in the report refresh",

close&&apply, create a new table by entering the formula

Table = UNION(Table1,Table2)

Thus, when you refresh from the data source, Table1 keep the original data, Table2 refresh with the new data, then the new table with a "union" function union the original data and new data.

Best Regards

Maggie

[Anonymous]

Hi Maggie,

My query is very big I am trying to implement your solution and I will let you know.

I have one observation at this stage. The process that I am trying to automate is ongoing, so it will be repeated more than twice.

I get that I can merge together 2 loadings, what about the upcoming third?

Table1 => Week1 Snapshot

Table2 => Week2 Snapshot

Table3 => Union(Table1, Table2)

Then I refresh Table2 that becomes Week3 Snapshot, Table1 will not be Week1 Snapshot + Week2 Snapshot.

Do you see what I mean?

[Anonymous]

Hi Maggie,

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.