Data moves when query refreshed / Aligning manually entered data with external data

There is no way to retain row relationship between queried table and manually entered column.

Only way to retain relationship is via formula.

If it must be manually entered... there are few ways to handle it.

1st Method
1. Using Get & Transform (PowerQuery/PQ), bring in the data from MS Access. Add Index column (or unique identifier column) if there isn't one already. Add as connection only for now.
2. Add blank query. Use List function to create list of manual entry and corresponding Index/UID.
Ex:

Please Login or Register  to view this content.

3. If you want to update record, you can just change Note value. If new item is to be added, you can add another [ColumnName=Value, Column2=Value] (i.e. Record).
4. Back in original query use "Merge" operation to Left join 2nd to original using left join. Load back to sheet.

Note: You may find maintaining 2nd list cumbersome. If that is the case just use Excel table as source and update info there.

2nd method:
If using any other query. You may want to create separate table with unique ID & Manual column (in Excel). Then link it to table using INDEX/MATCH or other lookup formula. Formula column should auto update with data brought in.

I tried this step by step and it seems that this is the 1st method you gave? however my columns get duplicated. I don't know where I get wrong, so I just delete it.

Also how do I add unique identifier column or count? Is this necessary? So that everytime the external data will refresh, new rows/values will be added at the bottom of the table?

Does adding a formula? it will vanish once the external data is refreshed. How it will be retain?

https://www.youtube.com/watch?v=duNY...ature=youtu.be

Without sample of your M query code, it's hard to say what you did wrong.

Marcel's method is more automated than what I suggested, instead of using List converted to table, he uses Previous Query to join onto original.

Post your M code and screenshot of your query table, before and after the operation. I can probably point out where you went wrong.

Posted in wrong thread.

this is my before code, i haven't change yet the #"Changed Type" to PQOutput

Please Login or Register  to view this content.

Updated to PQOutput

Please Login or Register  to view this content.

this is my after code:

Please Login or Register  to view this content.

so in the after code, my comment and formula column duplicated with the name of comment2 and formula2. I just delete it because those columns are the one that are not linked in my table. It works fine but I dont know where I have mistakes as I just followed the steps in the video. But if there its ok, I just leave it as is and will just delete it if it will not affect much in the file.

Also my other concern is that when I put formula in the formula column, it works fine but after I refreshed the external data, the formula vanish and convert it to value which is fine I think but I wanted to have a formula so that it goes well in the table once updated.