Manually adding column to Table loaded from Query then refresh

[WLou]

Hi all, 

I have loadaed a table from power query, and wish to add column "Comment" at the end of the table to mannually commenting them Row by Row

Ideally I will refresh this table every now and then to se me which rows are new and need to be commented 

What I have reliased is, once I click refresh, the comment column no longer reference to the original row, they just all over the place

What's the reason and any solution to that? I have a feeling this is just another dumb question 😞

In the past each row has unique keyreference so I can easily tell which are new, but this time there isn't one, maybe create index mannually?  

Thanks in advance 

Wendy

[ahmedoye]

If you could share some pictures showing your table and the query steps you currently have, it will be easier to help with this request. @WLou 

[WLou]

See example below 

the left 4 columns are part of the table loaded from power query

the last coloumn is a manually added comment section after data gets loaded 

once it has been refreshed, the comment section changed its order 

[edhans · Accepted solution]

You cannot keep an Excel column in sync with a Power Query column. If you are using a fomula that does the same thing to all rows ([Sales] * 1.05%) for example, that will work, but you are trying to keep individual comments tied to specific rows. You need to use Power Query to keep them in sync. See this file for what I've done, explained below.

1. Create a new table in Excel that has your comments. It also must have the column(s) that will be a unique link to the specific row you want I've used a combination of the Account Number and Journal Entry based on your screenshots.
2. Add a Comment field to your Comment table and make your comments.
3. Select your Comment table and load it to Power Query.
4. Click the first query in Power Query and on the Home ribbon, Merge as new.
5. Select your comment table.
6. Make sure the fields line up as shown here. You do this by selecting first Account Number, then Journal Entry. PQ will add the little 1/2. (You don't worry about this if you have one unique column, but here we need two columns for uniqueness)
   1. 
7. Once done, Expand the Comments table, and uncheck everything except the Comment field itself.
   1. 
8. You can see how this looks in Power Query's Dependency View in the View Tab.
   1. 
9. Exit Power Query. make sure the first two tables are not loaded, but load the new merged table.
10. It will look like this;
    1. 

Note: if your original table is comeing from Power Query, and yours may be, then modify my steps as follows:

#4 - In the query you are using to put your JEs to the workbook, just Merge, not Merge as New, with the comment field.

#8 - Your dependency view will look a little different.

#9 - Make sure your comment table is not loaded. But your original query is loaded and will now bring the comment field in.

Add comments as desired, and Refresh the queries.

Note: You could do this with XLOOKUP() as well, but I don't like mixing query work with VLOOKUP/XLOOKUP/INDEX-MATCH stuff as it will cause you problems down the line as you try to integrate more in, or you decided to move this to the Data Model or Power BI where no Excel functions work. Once in Power Query, stay in Power Query. 👍

[WLou]

Very helpful 

Thank you @edhans