Self join for adding comments to a table doesn't work

Hi dtiziani ,

The problem you're running into is common when mixing queried data and manual data entry in the same place. The issue is that your process creates an unstable loop. When you refresh, Power Query overwrites the data in the output table from the new CSV file, but it has no control over your separate, manually entered comment column. This refresh action breaks the row alignment between your data and your comments, causing the notes to appear next to the wrong items.

A much more stable solution is to completely separate your comments into their own dedicated table. This table will only be used as a source for your notes and will not be overwritten by the daily data refresh. In your Excel workbook, create a new sheet and on it, create an Excel Table (Ctrl+T) named something like tbl_Comments. This table should have only two columns: one for the Unique ID (using the same header name as in your main data, like RequestID) and one for your Comment. This is the single location where you will add and manage all your manual comments, ensuring each one is tied to a specific ID.

Your Power Query workflow should then be adjusted to use this new structure. You will have two main queries. The first query, which you can call SourceData, will be your existing query that connects to the daily CSV file and performs your transformations. You then create a second, new query called CommentsData that connects directly to the tbl_Comments you just made in your Excel workbook. It's best to set this CommentsData query to load as a "connection only" to keep your workbook clean.

With both queries set up, you will perform a merge. Open the SourceData query in the Power Query Editor. From the Home tab, select "Merge Queries". In the merge window, select your SourceData as the primary table and CommentsData as the second table to join. It is critical that you select the Unique ID column in both tables as the key for the join. Choose a Left Outer join to ensure all rows from your main CSV data are kept, even those without a matching comment.

After confirming the merge, a new column will appear in your SourceData query, likely named after your comments query. Click the expand icon on this new column's header. In the expansion options, deselect the unique ID (as you already have it) and ensure only your Comment column is checked. You can also uncheck the "Use original column name as prefix" box for a cleaner header name. Once you click OK, the comments will be added as a new column, correctly aligned with their corresponding data rows. You can then "Close & Load" this final query to your report sheet, and your comments will always refresh correctly. This method works because it joins data based on a reliable Unique ID rather than a fragile row position.

Best regards,