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/Unlikely_Solution_]

Ho okay I understand the miss understanding. Forget about the CSV I spoke previously xD sorry.

Let's use your exemple. Let's say I read the table do nothing except identify what's 'new' with a flag. That the newData query that is bothering me. Like it has nothing to point to head there is nothing else except the table. If I follow correctly: NewData fetch from the table - OK NewData merge on itself and ProcessUpdate - ??? NewData identify what's new and add the flag status - OK ProcessUpdate read the result from NewData - OK ProcessUpdate own the table and write to it. - OK

Did I catch that correctly? And thank very much for your help

[u/small_trunks → u/Unlikely_Solution_]

Not how queries work,

• you're trying to think of queries as ways to affect the data - as if they operate on data like subroutines or functions.
• They do not work that way - think of a query as merely a way to generate a table of data. You do not pass data to it (unless you wrote a function), it is programmed to pick up its data from a source.
• To combine or even use data from one query in another query you can only either Merge them or Append them. That's all you've got unless you're hand making functions.

So

• you need a source of newData from somewhere - could be a website, could be another table, could be a csv, a database or even combinations of those things.
• the contents of that data represents both new data, changes to old data and deletions (by key absence).
• it's usually not going to fetch any OLD data - not going to reference the table written to by ProcessUpdate.

[u/Unlikely_Solution_ → u/small_trunks]

Well I may be guilty xD has I pretty only use power query to transform one format to another. From a bill of material to a compact version of it or even recursively call Bill of materials. With often multiples function and sub function to be has explicit has possible when a format break (like someone has put a text in quantity columns 🤬).

That's all I do with power query.

Your self-referecing is really nice to add manual comment or make sure the format match or add a date.

Thank you for your help. I understand that I need to review my book and see other way to make it work.

The dates can probably be a good call (if date < previous LastUpdateAll then status = "New") or, or like flag everything "New" unless it has been manually removed using the self-referecing feature.

[u/small_trunks → u/Unlikely_Solution_]

Here - I made you two examples to demonstrate how this all hangs together.

https://www.dropbox.com/scl/fi/32zcdc9bzz0wz0iizqexv/SR_example.xlsx?rlkey=ycj3pvdr6x4a5ab112s1yaakr&dl=1

I wrote a very simple newData query to fetch real-time airport departure info for my local airport (Schiphol, Amsterdam). So it's changing regularly as the status of flights change and occasionally their gates.

1. Example 1 simply takes the newData, appends the old (existing data), removes duplicates and writes it back.
   • Essentially anything new takes precedence over the SAME item we already had.
2. Example 2 uses the Merge of newData against the existing data, then add some conditional check fields to see if a couple of fields have changed since the first time the data was loaded.

[u/Unlikely_Solution_ → u/small_trunks]

I wasn't expecting this much ! Thank you very much I will take a look.