Keep Audit Values with Records When Refreshing an Excel Power Query Table
Question details
Keep manually entered audit values associated with the correct records when an Excel Power Query table refreshes.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user adds audit values or comments to records displayed in a query output table and later refreshes the source data.
- Observed behavior
- When a refresh changes the row order, manually entered values can become associated with the wrong records.
Identify a stable, unique key for each record, such as a verified unique combination of Date, Name, and Invoice. Check that key values have consistent data types and formatting in both the source data and the audit table.
Merge a separate audit table with the source query
Use this approach to keep audit values independent of the refreshable output and match them to current source records by key.
On a separate worksheet, enter the key columns and an Audit or Comment column. Select the range and choose Insert > Table, then give the table a recognizable name on the Table Design tab.
Add one row per audited record to the separate table, entering its exact key values and audit comment. Edit this table rather than typing comments beside the query output.
Select a cell in the audit table and choose Data > From Table/Range. In Power Query, use Close & Load To and select Only Create Connection.
Open the source query from Data > Queries & Connections. In Power Query Editor, choose Home > Merge Queries, select the audit query, and select matching key columns in the same order in both tables. Choose Left Outer as the join kind.
Select the expand icon on the merged column, choose the Audit or Comment field, and select OK. Choose Home > Close & Load, then use Data > Refresh All when the data changes.
Store audit values in the source data
Use this option when you can edit the original source and want audit values to travel with each record before Power Query imports it.
Manage Spreadsheet Audit Tables with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not fix Microsoft Excel Power Query refresh behavior; check advanced query features before migrating a workbook.

Frequently Asked Questions
Why do audit comments move to the wrong records after refresh?
A refresh can reorder, add, or remove query rows, while manually entered values beside the output may remain in worksheet positions. Match comments to records by a stable key instead of row position.
Can I use Date, Name, and Invoice as the key?
Yes, if their combination uniquely and consistently identifies each record. In Merge Queries, select those columns in the same order in both tables and make sure their data types and values match.
What happens to a new record that has no audit entry?
A Left Outer merge keeps the new source record and returns a blank audit value. Add its key and comment to the separate audit table, then choose Data > Refresh All.
Will the separate audit table retain comments for records removed from the source?
Yes, the audit entries remain in the separate table. They will not appear in the merged output while their matching source records are absent.
Why does a record appear more than once after the merge?
The audit table may contain multiple rows with the same key. Check the key columns in the audit table and remove or resolve duplicate entries before refreshing.




