18 - Archive table(s) with Power Query (Excel)
How to take multiple tables in your workbook and add them to an archive table.
Part 1 will solve the question "how do I copy-paste all rows from these tables into an archive, based on a condition?". The original rows aren't deleted.
The archive table will create a "snapshot" archive. Next time you update your queries, the original archived rows will be deleted. You'll likely want to create a copy of this sheet and store that in an archive workbook.
Part 2 solves the question "how do I copy-paste all rows from these tables into an archive, based on a condtion, AND ALSO delete the rows from my original tables?"
This involves circular references. You first update the Archive-query, which copy-pastes rows based on a condition.
Then you update all the Data-queries, which are actually the tables you write data into. Because your query refers to itself, it will apply the filter-condition to its own rows - deleting them.
If you want to use Part 2 - which I don't recommend - you need to 100% guarantee that you always update your queries in the correct order. Your workflow will be:
1) Update the Archive-query
2) Create a copy of the Archive-sheet in another workbook
3) Update all the Data-queries to delete the archived rows
If you mess up the order, you will delete data before saving it.
I would always solve this problem with VBA, since I can guarantee that this order is followed. Your solution cannot require the user to never make mistakes.
See https://www.nngroup.com/articles/slips/
Plan for mistakes.
00:00 - Part 1 - simple
03:35 - Part 2 - circular references
Watch video 18 - Archive table(s) with Power Query (Excel) online without registration, duration hours minute second in high quality. This video was added by user WesleySon 14 May 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 856 once and liked it 4 people.