Power Query Problems

Excel Power Query: Automatically Append Summary Values After Each Update

Muhammad TalhaMuhammad Talha Nov 12, 2026 868 views

Question details

The user needs to record updated Excel summary values in a history table without manually copying and pasting or creating duplicate entries.

Excel Power Query: Automatically Append Summary Values After Each Update
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the update-date field

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.

2
Prepare the summary and history tables

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.

3
Load the sources into Power Query

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.

4
Filter out dates already in the history

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.

5
Append and refresh the history result

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.

Prevent duplicate dates: Use the date column as the matching key and ensure both the current summary and history tables use the same date format. A refresh will then add only dates that are not already present.
Free Microsoft Office alternative

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. 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. 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. 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.
Free and lightweight spreadsheet software for maintaining summary and history tablesFamiliar worksheet, table, filtering, and formula interface for easier migrationCompatible with Microsoft Excel XLSX files for sharing and continued editingSupports common spreadsheet calculations and data organization workflows
QA img-9

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.