Excel Power Query: Keep Custom Notes Aligned After Refresh
Question details
The user needs to keep manually maintained notes associated with the correct records when external data is refreshed through Power Query.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user has added custom notes beside a Power Query output and wants those notes to remain matched when records are added, removed, or reordered.
- Observed behavior
- Manually added worksheet columns can become misaligned with the refreshed query results because they are tied to row positions rather than stable record identifiers.
Identify a column that uniquely identifies each record, such as Item ID, and confirm that the notes table uses the same key with one row per record. Keep the notes outside the Power Query output so the refresh process does not overwrite or reorder them unpredictably.
Merge a keyed notes table with the refreshed Power Query results
Use this method when notes must stay attached to records even after the source data changes order or row count.
A worksheet column placed beside a Power Query output is not synchronized with the records returned by the query. Store notes in a separate Excel table and merge it with the query using a stable key instead of relying on row position.
In Excel, select the notes range and choose Insert > Table. Include the stable identifier, such as Item ID, and a Notes column. Give the table a clear name, such as RecordNotes.
Select the source data and choose Data > From Table/Range to open it in Power Query. Repeat the process for the RecordNotes table so both datasets are available as queries.
In Power Query, open the refreshed data query and select Home > Merge Queries. Choose RecordNotes as the second table, select the matching Item ID columns in both tables, and use a Left Outer join.
Click the expand button on the merged table column, select Notes, and clear the option to use the original column name as a prefix if preferred. Then choose Home > Close & Load to return the combined results to Excel.
Use Data > Refresh All and confirm that each note remains beside the matching Item ID, even when the source adds, removes, or reorders records.
Use a self-referencing Power Query table carefully
Consider this advanced approach when notes must be maintained within a query-driven workflow and you can control refresh dependencies.
Manage keyed data and notes with a lightweight WPS Office workflow
WPS Office is a free, lightweight Microsoft Office alternative for organizing source data, notes, and keyed lookup tables in familiar spreadsheet workbooks. It supports common Excel formats for a smoother migration, although Microsoft Excel Power Query-specific refresh features remain an Excel function.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, and select Open to load the existing .xlsx or .xls workbook.
- 2. Create a keyed notes table: Add an Item ID column and a Notes column on a separate worksheet, then use Insert > Table to organize the editable notes.
- 3. Review records by key: Use formulas such as VLOOKUP or XLOOKUP, when supported by the workbook version, to display notes beside matching Item IDs rather than matching rows by position.
Frequently Asked Questions
Why do notes move to the wrong records after a Power Query refresh?
Manually added worksheet columns are positioned by row number, while Power Query can add, remove, filter, or reorder rows during refresh. The notes therefore no longer match the original records.
Where should I store notes that belong to Power Query records?
Store them in a separate Excel table containing a stable key, such as Item ID, and a Notes column. Do not place them directly beside the query output.
How do I merge notes with Power Query data?
Load both the source table and the notes table into Power Query, choose Home > Merge Queries, match the Item ID columns, select a Left Outer join, and expand the Notes column.
Can I use a row number instead of an Item ID?
A row number is unreliable because refreshes can change row order or insert records. Use a unique, stable identifier from the source whenever possible.
Is a self-referencing Power Query table safe for storing notes?
It can work in an advanced design, but circular dependencies may cause refresh failures. A separate keyed notes table merged into the refreshed query is usually safer and easier to maintain.
