cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ellenne Frequent Visitor
Frequent Visitor

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 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Advanced Incremental Load

Hi @ellenne

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)

3.png

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

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Advanced Incremental Load

Hi @ellenne

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)

3.png

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

 

ellenne Frequent Visitor
Frequent Visitor

Re: Advanced Incremental Load

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?

Highlighted
ellenne Frequent Visitor
Frequent Visitor

Re: Advanced Incremental Load

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 43 members 1,074 guests
Please welcome our newest community members: