Excel Power Query: Automatically Append Summary Values After Each Update
Question details
The user needs to record updated Excel summary values in a history table without manually copying and pasting or creating duplicate entries.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A workbook contains raw data, summary values, a last-update date, and a history table. The user wants Power Query to add one history row whenever the date changes.
- Observed behavior
- The summary values are not automatically appended to a dated history table after each update, and manual copying is required to preserve the results.
Create a dedicated cell or named range for the workbook's last-update date, and confirm that the summary values and existing history records are formatted as Excel tables. Make sure the history source is separate from the query output to avoid a circular Power Query reference.
Use the update date to append only new summary records
Use this method when the workbook should add one summary row for each new update date and skip dates already recorded in the history table.
Power Query can read the current summary values, the last-update date, and the existing history table. Filter the current summary so it is appended only when its date does not already exist in the history source. The query should output the combined result to a separate table or worksheet.
Power Query refreshes and transforms data, but it does not permanently write back to its own source table. Keep the history input separate from the query output, or use an external storage table for records that must persist between refreshes.
Enter the latest update date in a dedicated worksheet cell, such as Summary!B1, or create a named range such as LastUpdateDate by selecting the cell and choosing Formulas > Define Name.
Select the summary range and choose Insert > Table, then name it in Table Design, such as CurrentSummary. Select the existing log and create another Excel table named HistorySource with a matching UpdateDate column and the required summary columns.
Select each table and choose Data > From Table/Range. In Power Query Editor, verify that the update date and summary columns have the correct data types, then choose Home > Close & Load To to load the queries as connections or staging tables.
In the current-summary query, use Home > Merge Queries to compare its UpdateDate with HistorySource[UpdateDate]. Expand the merged result if needed, filter to rows with no matching history date, and keep the new summary columns.
Choose Home > Append Queries to combine the existing history rows with the filtered new rows, then select Home > Close & Load To and load the result to a separate history-output worksheet. After changing the date or raw data, choose Data > Refresh All.
Refresh the query automatically when the workbook opens
Use this option when the history process should run whenever the workbook is opened instead of relying on a manual Refresh All command.
Move Excel Summary Tracking Workflows to WPS Office
WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface and strong compatibility with Excel XLSX files. It is useful for reviewing, editing, and maintaining summary and history tables, while advanced Microsoft Power Query automation may still require Excel.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, select Open, and load the Excel .xlsx workbook containing the summary and history tables.
- 2. Review the summary and history tables: Use the worksheet tabs and table ranges to verify the update-date column, summary values, and existing history records before editing.
- 3. Save a compatible copy: Choose Menu > Save As, select the .xlsx format, and save the workbook so it can continue to be opened in Microsoft Excel when Power Query automation is required.

Frequently Asked Questions
Can Power Query append a new summary row without duplicating an existing date?
Yes. Merge the current summary query with the history source using the update-date column, then filter out rows with a matching date before appending the remaining rows.
Where should the last-update date be stored?
Store it in a dedicated worksheet cell or named range, such as LastUpdateDate. Power Query can read that value and use it as the UpdateDate for the current summary record.
Why should the query output not also be its source history table?
A query cannot safely read from and overwrite the same table during refresh. Use a separate history source, staging table, or external storage location to preserve earlier records.
How do I refresh the summary history automatically when Excel opens?
Open Data > Queries & Connections, right-click the query, choose Properties, select Refresh data when opening the file on the Usage tab, and click OK.
What happens if the update date is unchanged?
The date comparison finds an existing history record, so the current summary is excluded from the append step and no duplicate row is added.




