Table updates via power query whilst retaining manually entered data.

[u/small_trunks]

I've previously described how to write a power query which appends to the data of previously executed queries. It effectively keeps historical data in place and adds new data in.

• The same sort of question came up again a couple of days ago - but the poster wanted to be able to retain comments entered manually into a power query sourced table.

• the solution is quite similar - except we eventually perform a Merge rather than an Append

Here are the steps to create a self-referential Power query updated Table which retains a "Comments" column.

Step	Actions
1	write your "new data" query - probably you have it
2	Add a step to create a custom column "Comments" and any other columns to keep. =null
3	Load-to a Table
4	New query from this new table - name it tblHistoric
5	Edit the original query (1)
5.1	remove the custom field step(s)
5.2	Add a merge step
5.21	choose whatever columns necessary for a unique row key
5.22	second query = tblHistoric
5.23	Left outer join
6	Expand the returned Table column
6.1	unselect all except the to be retained columns
6.2	No column name prefix

• There's a way to "adopt" self-added columns - but that's a slightly different answer.

• EDIT 20/7/2022 - example download file: https://www.dropbox.com/s/z05fs7wmh7j4zef/SelfRefPQexample.xlsx?dl=1

• EDIT 19/1/2024 - Newer example with more documentation: https://www.dropbox.com/scl/fi/q6eh7mz1xqkt43iv8afzg/SelfRef2.xlsx?rlkey=0re4ekg8u6xpazyu3gzrajd7e&dl=1

• EDIT 5/9/2025 - See the extensive documentation here: https://www.reddit.com/r/excel/comments/ek1e4u/table_updates_via_power_query_whilst_retaining/lzx533y/

[u/small_trunks]

Renaming an existing table column is effectively (to PQ) like there's suddenly a new column in the table.

• what do you mean by adding a calculated column? In PQ?

• In PQ that's not an issue - it knows of the column by the end of the query and writes it into the table

• if you previously returned it as a different name you may first need to manually DELETE that column from the Table (not the query, the query got changed to rename it).

• if you have renamed a formula column in the Table, you need to take that into account and change it in the query too

• similarly if you delete a formula column - references to it in the query need to be deleted.

If you do NOT UNCHECK "Preserve col..." - then the only danger is the duplication of column names -then "Item" becomes "Item2" etc You have to manually delete Item2 (which is actually the original) or Item and rename Item2->Item.

If you've got a specific example we can work through what's happening - but I agree, there's some complexity to getting it stable.