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

[dtiziani]

Hi my name is Davide and I've been using Power Query for only 6 months

i use it to merge different tables extracted by our SAP and Aras PLM

i'm sorry i can't share files for privacy issues

i export updated data from the PLM every day to discover where a process is stopped. the process has 10 steps

i wanted to add a manual comment to each row (waiting for this, it will be completed then, ask for something etc)

i found some tutorials that said:

- import data from CSV

- add column with comment

- import data from sheet /table (the data from the previous sheet, to create a new query)

- rename new query adding "with comments" and load only to a connection

- do a self join of the first query with the second

- eplode the table and load only the notes column

i did it the first time and i think it worked

then i had to redo the job due to some changes and i can't make it work any more

what i see is this:

i create the excel sheet importing a csv with 1000 rows and do the above process

then i update the CSV file imported let's say with an extra 300 rows

when i click on "refresh all" the first time i see that the column notes coming from the join is correct (right note to the right request) but the column note from the original table is broken (note on different row)

then i click refresh again and also the column note from the join has wrong notes (note in the wrong row like in the original table)

the added rows in the csv file move the notes down.

the first column has unique values.

i tried several times but i can't figure where the problem is. i assume it's a stupid mistake of mine

thank you for the help

Davide

[lbendlin]

Self Referencing exists but it works different from what you describe.  In your Desktop Power Query you would connect to Analysis Services and point to your own semantic model in the service to retrieve the "prior" state and do your snapshot/comment shenanigans.  As @DataNinja777 was saying, this is highly unstable and highly not recommended.

[DataNinja777 · Accepted solution]

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,

[dtiziani]

Hi

thank you for your detailed explanation. i actually already tried it and in fact it works. it was my plan B since it involves a manual copy of the Id from source table into the comment table. it's a bit invonvenient to input data into one table and read it into another.

but if you say it's the only safe option I'll accept it and I'll do that.

thank you

Davide

[lbendlin]

Consider Translytical Task Flows as an alternative.  Requires Fabric.