Power Query Problems

Keep Audit Values with Records When Refreshing an Excel Power Query Table

Tauseeq MagsiTauseeq Magsi Nov 14, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Create an audit table

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.

2
Enter audit values by key

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.

3
Load the audit table into Power Query

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.

4
Merge the queries

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.

5
Expand and load the results

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.

Keep keys unique: The audit table should contain no more than one row per key; duplicate matches can duplicate source rows. New records will show a blank audit value until you add their keys and comments to the audit table.
Free Microsoft Office alternative

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.

Organize record keys and audit comments in spreadsheet tablesOpen and edit common Microsoft Excel formats, including XLSXUse a familiar interface for everyday spreadsheet workMove routine spreadsheet work to a free, lightweight office suite
microsoft office alternative - wps office

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.