Create a Self-Referencing Table in Excel Power Query
Question details
The user wants to retain editable comments alongside Power Query data when the query refreshes.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A worksheet contains refreshed records and a comments column that users edit directly.
- Observed behavior
- Comments should remain associated with the correct records after refresh, even if the source data changes.
Ensure each source record has a unique, stable ID that does not change when rows are sorted or refreshed. The example assumes the source table is named SourceData, has an ID column, and does not already have a Comment column.
Merge the Previous Output Table with the Refreshed Source
Use this self-referencing approach to bring worksheet comments back into the query output by matching each comment to its record ID.
The query reads the existing worksheet output as a table before replacing it with refreshed results. The ID and Comment columns from that output are merged with the current source data.
The source and output IDs must be unique, nonblank, and stored using matching data types. A row number is suitable only if it remains permanently assigned to the same record.
Select a cell in SourceData, then choose Data > From Table/Range. In Power Query Editor, choose Add Column > Custom Column, name it Comment, and enter null as the formula.
Choose Home > Close & Load to load the query to a worksheet. Select the resulting table and use Table Design > Table Name to name it PQ_Output. Enter any initial comments directly in its Comment column.
Open the query from Data > Queries & Connections, choose Home > Advanced Editor, and replace the code with the following. Adjust table and column names if yours differ: let Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content], PreviousOutput = Excel.CurrentWorkbook(){[Name="PQ_Output"]}[Content], Comments = Table.SelectColumns(PreviousOutput, {"ID", "Comment"}), Merged = Table.NestedJoin(Source, {"ID"}, Comments, {"ID"}, "Previous", JoinKind.LeftOuter), Result = Table.ExpandTableColumn(Merged, "Previous", {"Comment"}, {"Comment"}) in Result
Choose Done, then Home > Close & Load to return the updated query to its existing output table. Edit a comment in PQ_Output, change or reorder source records, and use Data > Refresh All to check that the comment remains with the same ID.

Work with Spreadsheet Tables in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not fix or reproduce this Excel Power Query self-referencing workflow.

Frequently Asked Questions
Why do I need a stable ID for the merge?
Power Query matches comments to records through the ID columns in Table.NestedJoin. If an ID changes or is reused, a comment may be lost or attached to the wrong record.
Can I use the worksheet row number as the ID?
Use a row number only if it is permanently assigned to the record. A position-based number that changes after sorting or refresh cannot reliably match comments.
Where should I enter comments after setting up the query?
Type comments directly into the Comment column of the PQ_Output worksheet table, then use Data > Refresh All when you want to update the source records.
What happens to comments for new or deleted records?
New IDs appear with blank comments. If an ID is absent during refresh, its row and comment are removed from the output, so the comment will not automatically return if the record reappears later.
What should I check if the query cannot find a table?
Select each worksheet table and check its name under Table Design > Table Name. The names must match SourceData and PQ_Output in the Advanced Editor code, or you must update the code to match your table names.




