Power query Help - create a Historical table to apend only new expopoted data

[Lisay405]

Hello Experts! I'm seeking help to be able to create a history table that will keep the previous week total to and append the current week total. I need to be able to keep the history of the existing query tables with out overwriting pervious data after new current data is export from SAP and refreshed in the main output tables.
Previous table
Week # | Total kgs at risk
21 | 70,000
New data append
Week # | total kgs at risk
22 | 85,000

This information is required to update a chart to show the total amount of at risk kgs for each week in the year.

Would very much appreciate any help to solve this issue for me. Thank you!

 

[alansidman]

 

[Lisay405]

My export data changes to new data each export the previous data is not saved as the SAP export is saved by replace existing file with each export. As soon as I refresh ecxel query all previous data is replaced with new data. I've have actually watched those videos...thanks for responding.

 

[Alex Blakenburg]

I am not sure that there is any other option other than storing a copy of the previous data somewhere and then to run an append query. (Assuming the data does not overlap).
So either in the same Excel workbook have a previous week YTD sheet / table or access a previous Excel Workbook or export the data to a file.
The easiest in my view would be to copy it either manually or via VBA to a previous Week YTD Sheet / Table before you get the latest data.
Then instead of using your current query have an append query that gets the previous data and appends the new data to it and outputs it to a current sheet / table.

You should always take a copy of the workbook before running the refresh though.

 

[Lisay405]

I am not sure that there is any other option other than storing a copy of the previous data somewhere and then to run an append query. (Assuming the data does not overlap).
So either in the same Excel workbook have a previous week YTD sheet / table or access a previous Excel Workbook or export the data to a file.
The easiest in my view would be to copy it either manually or via VBA to a previous Week YTD Sheet / Table before you get the latest data.
Then instead of using your current query have an append query that gets the previous data and appends the new data to it and outputs it to a current sheet / table.

You should always take a copy of the workbook before running the refresh though.

Thank you I will try that option.

 

[Lisay405]

I found a simple solution....using power automate desktop to copy worksheet...rewrite to new workbook..save and rename as the week # then ill make a new cinection to grab all the worksheets and append.

 

[Alex Blakenburg]

You may want to consider creating an Archive copy of the file with the query deleted. This will prevent someone accidentally refreshing the query in the historical file overwriting your data.
Making the file read only might work as well.