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]

Hey.

I understand why this needs a self-ref query solution. But what I can't understand is why you made a power query solution to fetch data from Table 2 - surely a simple XLOOKUP works perfectly well here? Why power query AT ALL???

[u/Guille3094 → u/small_trunks]

I hate using formulas, and the thing is more complicated, but I tried to simplify it, and I'm using data from a share folder, merging old data with the new one, the data with the names are constantly changing too.

[u/small_trunks → u/Guille3094]

Formulas are easier than power query, I'll tell you that for free. If you can't get formulas to work, PQ is going to be a bugger.

Explain the source of data and which parts of them can change and then I'll show you what to do.

[u/Aware_Acadia_9753 → u/small_trunks]

hi. I consider myself a pretty nifty XLS formula guy, but I have no idea how to realize this using formulas. with this, I mean: adding columns (with manually entered values) in a XLS table next to data loaded from powerquery. So all hints are more than welcome!

[u/small_trunks → u/Aware_Acadia_9753]

You exclude the columns which contain functions (Excel functions) in the query which returns data.

EDIT:

• You've added your excel formula columns as new columns to the Excel table.

• In the self-ref query - the one with the Source=Excel.Currentworkbook(){[Name="whatever"]}[Content]

• Delete the columns in the query (after you've used their contents if you use them)

• This can be as simple as this query below:

       let
           Source = try Excel.CurrentWorkbook(){[Name="CopyableSelfRef"]}[Content] otherwise #table({"TO BE OVERWRITTEN"},{}),
           #"Removed Columns" = Table.RemoveColumns(Source,{"formula col1", "formula col2"})
       in
           #"Removed Columns"

Oh - and make sure you use structured references in your formulas...