How to Keep Historical Power Query Results After Each Refresh in Excel
Question details
The user wants to retain historical data snapshots in a Power Query report after every refresh, preventing new data from overwriting previous results.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking historical totals and snapshots over time without losing previous refresh data when generating reports from CSV and XLSX files.
- Observed behavior
- By default, Power Query overwrites existing destination data upon refresh, which prevents the tracking of historical trends and snapshots.
Ensure your source data queries are functioning correctly and back up your current workbook before setting up a self-referencing or appending historical query to avoid circular reference errors.
Create an Appending Historical Query
Set up Power Query to combine your previous results with the latest data snapshot using connection-only queries.
By splitting your query into an Old (existing data) and New (latest refresh) query, you can append them together. This ensures past data is retained while new records are added on each refresh.
Run your initial query and load the results to an Excel Table named 'Result'.
In Power Query, create a connection-only reference to this table and name it 'Result Old'. Then, duplicate your main data processing query, name it 'Result New', and also set it to connection-only.
Create a new query and use the 'Append Queries' feature from the Home tab. Choose to append 'Result Old' and 'Result New'. This will stack the new results under the previous results.
Before loading the appended data, go to Add Column > Custom Column and use the formula 'DateTime.LocalNow()' to stamp the rows with the current refresh time.
Close and Load this combined query to a new Excel table. This table will now serve as your growing historical record.
Use Excel Tables for Dynamic Chart Updates
Ensure your historical data output is formatted as an Excel Table so that any connected charts or PivotTables update automatically when new rows are appended.
Process and Analyze Data Efficiently with WPS Office
If managing complex Power Query setups and self-referencing tables in Excel becomes overwhelming, consider WPS Office. It provides a lightweight, highly compatible spreadsheet tool equipped with robust data analysis features, pivot tables, and charting capabilities.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free suite and follow the installation prompts.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx or .csv data files directly with full formatting retained.
- 3. Track Data with PivotTables: Use the Data tab and built-in PivotTable tools to efficiently summarize, append, and visualize your historical data records.
Frequently Asked Questions
Why does Power Query overwrite my data on refresh?
By default, Power Query is designed to clear the destination table and replace it with the latest data pulled from the source. To keep history, you must configure a self-referencing query or append new data to an independently saved historical table.
Will a self-referencing query cause an infinite loop in Excel?
It can if not set up properly. To prevent circular reference errors or uncontrolled data duplication, it is crucial to keep the source table and the final output table strictly separated using connection-only queries for the intermediate steps.
How do I add a timestamp to my historical Power Query data?
You can add a Custom Column in the Power Query Editor using the formula DateTime.LocalNow(). This will stamp each row processed during the refresh with the current date and time, making it easy to identify historical snapshots.
