This user's question gets at a fundamental tension in how we think about data: is data a snapshot, or is it a story? Right now, Excel's Power Query treats every refresh as a clean exchange, new records in, old records out. That works fine when the source file is the single source of truth. But for this user, it's not. They receive a weekly source file that may drop records that are still relevant to their own analysis. They need the old rows to persist. They need history, not just the latest picture.
The practical problem is clear: Power Query is designed to mirror a source, not to maintain an independent record. When you refresh, it reapplies the same transformations to the current source data. If a record no longer exists in that source, it vanishes from your output. There is no native toggle in Power Query to say, "Keep everything you've ever seen, and only add what's new." This is a design choice, not a limitation of the technology. It assumes the source is authoritative and complete. For many workflows, that assumption is wrong.
What this means for anyone working with recurring data files, weekly inventories, monthly reports, incremental exports, is that you need a workaround. The common approach is to maintain a separate "history" table in your workbook. Load the new data into a staging query, then use a merge operation to append only new rows to your existing table, based on a unique identifier. It is not a one-click solution, and it requires some comfort with the Power Query editor. But it is reliable, and it does not depend on any external add-in or service. For the user who asked this question, the answer is yes, it is possible, but it requires building a pattern that the tool does not offer by default.
That gap, between what a tool does and what a user needs, is exactly where innovation matters. An AI-native spreadsheet should not make you build a workaround for something as basic as preserving history. It should understand that your data is cumulative. It should let you say, "Refresh this, but keep everything I've ever pulled in." That is not a niche request. It is a core expectation for anyone who treats spreadsheets as living records, not disposable reports. The next generation of tools will not ask you to choose between a clean refresh and a complete history. They will give you both, because that is what the work actually demands.