Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Advanced Incremental Load

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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.