Power Query Problems

Create a Self-Referencing Table in Excel Power Query

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

The user wants to retain editable comments alongside Power Query data when the query refreshes.

Create a Self-Referencing Table in Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the initial query

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.

2
Load and name the output

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.

3
Replace the query code

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

4
Load and test the refresh

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.

Merge the Previous Output Table with the Refreshed Source
Comments for removed records: A comment is retained only while its ID is present in the output. If a source record disappears and the query is refreshed, its comment is not preserved for a later return.
Free Microsoft Office alternative

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.

Edit spreadsheet tables and comments in a familiar interface.Open and save common Microsoft Excel formats, including XLSX.Use a free, lightweight suite for everyday document work.Move existing Office files into a familiar workspace.
microsoft office alternative - wps office

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.