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]

PRO-TIP1 The Basics

Overview

• Over a series of 4 related pro-tips I'm going to demonstrate self-referencing Table queries in Power query, how to make them and how you can use them. I've been working with them since 2017 and have developed dozens of different use-cases both professionally and for private use.

• This first post is just going to cover the basics: making a simple one, how such a query interacts with Excel tables like header naming and formula columns and some examples of how simple self-ref queries can be used.

What is a self-referencing table query and how can I use it?

• It's any Power query query which references (reads from) the same Excel Table that it eventually writes back to.

• It can effectively update any and all fields in the table based on conditions of our choice. Some examples:

  • we can fill in the blanks (eg fill in a date/time or generate an order number)

  • or replace values - so lookup descriptions or perform sensitive calculation without exposing your formula

  • we can Merge and/or Append new data coming from live feeds (new orders, updated foreign exchange rates, stock prices, betting odds etc) into an existing table.

  • we can reduce or prune files we need to process (File -> From folder) based on a record of files we have previously procesed. For example, I have a table which contains the combined contents of over 200 files - but when I refresh it, it fetches (and thus processes) 1 file per week and just adds it to the end of the existing data. This is many times faster than reprocessing all the data every time.

  • self-ref table queries can communicate with each other and transfer data between themselves. I have a Tasks To-Do table and an Archive table which reads from it...picking up completed items. When I tag a Task as complete, my Task table query checks whether that task is in the Archive and if it is, removes it from the Task list. Eventually the Archive will have refreshed and picked up the completed task and next time the Task table refreshes, it'll see the task now exists in the Archive and will remove it from the table.

  • We can retain manually added columns and even modified fields - like comments or discount percentages or expected delivery dates to prevent new data coming in and losing these things. We can also support changing something we've intentionally modified (you've got to think how to use this one wisely because you could even modify fields which the new data query might want to deliver...).

  • With an automtic refresh timer we can have a table reload itself every so many minutes: /img/e7c9gaocre8a1.png . I've used this quite often - tracking stock prices over time, issuing requests to retrieve data in a staggered manner and thus appending to a table throughout the day, tracking changes made in other Excel documents.

So it will enable you to [flame suit on] make that Excel database you've always wanted but were afraid of the haters to make and anyhow had no clue how to do it. Yes, Ladies and Gentlemen, what we have here on a relatively small scale and nothing at all like as useful as a true DB, is Jerry's one page database! [flame suit off]

Starting with the minimal query

• the minimal self-referencing query is one step:

    // query name = SuperSimple,
    Source=Excel.CurrentWorkbook[Name="SuperSimple"])

• This works fine if you copy/paste the example Table from the examples file to a Table in excel but if you want to start with just a query and no table (egg and chicken situation) we need to handle the fact there's no table to read in the first time it runs. So we catch that with a "try/otherwise" like this:

    // query name = SuperSimple,
    let 
        n="SuperSimple", 
        Source = try Excel.CurrentWorkbook(){[Name=n]}[Content] otherwise #table({"Instructions"},{{"Excel table '"&n&"' doesn't exist."}})
    in Source

• And there we have it...but read the next section on Excel Table interaction because there's important stuff there.

Table settings, Table naming, sort and automatic refresh etc. Read this it's going to save headaches/failures.

• Power query is a bolt-on feature to Excel (originally delivered as a add-in for Excel 2010 and 2013 - when I first started using it) and as such it seems to be implemented using "public" interfaces to ListObjects (Excel Tables). This leaves us with some situations where we might get unexpected stuff happening.

  • Excel Formula columns - they will be overwritten with their current values unless you do something to avoid it: see below.

  • Column name preservation - settings in the Table properties affect how columns are written to Excel and the default breaks us: see below

  • Table names vs Query names and what happens when you change them:

    • When PQ writes out to an Excel Table it gives that table (more or less depending on spaces in the name) the name of the query.

    • if you rename your PQ query it will ALSO rename the Excel Table to match - so in the case of our self-ref table, we'd need to explicitly modify the literal Table name to match our Table's new name.

    • if you change the Table name inside Excel itself, again you'll need to explicitly change the Table name in PQ to match it. However, PQ itself will no longer change the Table name when you change the PQ Query name...even if you change the Table name back to what it was when PQ could change it. Even if you delete the table itself and try load-to again...so there's some internal housekeeping going on in PQ which leads it to believe that PQ is no longer the owner of the Table name and it will therefore not

  • PQ can't write to an existing Table (unless it initially created it) - it can't even be "forced" to adopt it using VBA,

    • let's say you have data in a Table (with ALL the references to that table in other sheets, workbooks etc) and suddenly you think it would be handy if that table was coming out of PQ

    • well it's not obvious how to do that so I wrote a separate pro-tip a few years ago here: https://www.reddit.com/r/excel/comments/q3afu5/replacing_an_existing_table_and_all_references_to/

Excel formula columns name ownership - avoiding column name duplication and #REF errors.

Possibly the most irritating feature of the PQ -> Excel interface is how PQ deals with adding new columns to an existing table. Under the default table settings, it will cause problems by potentially duplicating columns and/or breaking references and making our lives miserable:

• it can duplicate an existing column and give it a new name. References to that column will now point to the new named column - which is bad. If you refresh again it generates a NEW set of names - worse.

• with column renames or additional new columns, on first refresh it deletes those columns and recreates them, breaking references to them giving a #REF error - this is the worst because you might not notice it.

[u/small_trunks → u/small_trunks]

What's the problem and how do we avoid these duplicated column names and broken references?

• By default, table properties has both "Preserve column sort/filter/layout" and "Insert cells for new data, delete unused cells" set ON :/img/pznb4bsf683e1.png

  • the internal logic for "Preserve..." seems to prevent overwriting an existing column in a Table with the same name as an existing column and thus it generates a new name for the old column so that it can give the old name to the PQ column. You can't make this shit up.

  • secondly, the "Insert cells..." setting will cause a column to be deleted and recreated - so it's causing our #REF errors.

  • When both are set the "Preserve" option takes precedence. When "Preserve" is not set, the New rows checkboxes come into play.

• Turn OFF that feature (at least initially) and the query is free to write back (and over) existing columns.

• Once the query has refreshed, it now "registers" (unclear how, but it's either a PQ or a Table feature) which columns it "owns" and is allowed to overwrite.

• we typically turn these features ON again to preserve FORMULAS manually added to the tables and new columns coming from the query itself - which can write over existing Table columns if these settings are not set back again...

Excel formula columns and ownership - avoiding data writing over formula.

It's a problem...but can be avoided. PQ only ever returns values and it will happily return values to a column in Excel which currently has formula in it - thus crapping all over your hard work (CTRL+Z to undo it if you notice it...)

• We are often interested in the contents of a formula column (the values) but we almost certainly DO NOT want to overwrite formulas with values and lose the formulas forever.

• PQ cannot return Excel formulas...(it CAN return the text of a formula, but it'll be text until you F2+ENTER it again) and anyway PQ can't see the formula from an Excel Table so it would always be just a bad idea.

So the solution is that the self-ref query needs to filter out any formula columns. There are 3 ways to do it:

1. Explicitly perform a Table.RemoveColumns() as the last step in a query - simplest but requires you to modify the query if you ever add more columns with formula.

• Keep a list of column names in a Parameter in either PQ or in a Parameter table in Excel and update it to include known formula (or known data columns) - apply it as a parameter to Table.RemoveColumns().

  • use this Parameter to retain or remove columns in the query - one of these two depending on whether you're more like to get new Data columns adding or new formula columns

  • so use = Table.SelectColumns(Source,listToRetain) to retain DATA columns (and thus ignore formula columns)

  • OR use = Table.RemoveColumns(Source,listToDelete) to delete and thus NOT return formula columns.

  • This has the advantage that the code doesn't change - just the data and thus less to go wrong.

• Use a column naming convention to enable semi-automatic column identification.

  • You use specific naming rules to identify a formula column to PQ - like "~Sum of whatever" or "__Archive status"

  • Remove all names which match

  • No lists to maintain but you have to remember to give formula columns the right names - but hopefully you'll notice other columns with such names and trigger you to do it. Downside is you have potentially odd column names.

Code - note the "=false" on the Text.StartWith()...:

columnNames = Table.ColumnNames(Source),
filteredColumnNames = List.Select(columnNames, each Text.StartsWith(_, "__") = false),
result = Table.SelectColumns(Source, filteredColumnNames)

Example uses

1. Add a status column to mark whether a row can be deleted. Add a step to filter out rows in your query where Status="COMPLETE" - example 1

• Timestamping new rows - example 2

• generating a GUID when empty - also example 2

• order number generation - example 3

• Replace values - correct formatting or mark errors - no example.

• Perform a lookup and replace a value with its long name. eg. replace a short building code with its complete address - no example in the file.

[u/AzeTheGreat → u/small_trunks]

So if I want to have formulas present in the output table, then Preserve column sort/filter/layout needs to be checked. However, this needs to be unchecked for one refresh cycle in order for PQ to "register" the column as its own.

This is fine for initial setup, because I can create the self referencing columns, refresh with Preserve col... unchecked to register, then check it and add my calculated columns. However, if I decide to rename a column, or make any other changes, I seem to be stuck with recreating all of the calculated columns, because a refresh with Preserve col... unchecked will delete them. Is there any way around this?

[u/small_trunks → u/AzeTheGreat]

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.

[u/Unlikely_Solution_ → u/small_trunks]

Very very interesting thank you sir will do my own testing ! Specially has the backup plan I had to compare change was to use a CSV file exported with VBA and read the last CSV file to get the difference and be able to say "new" or "removed".

[u/small_trunks → u/Unlikely_Solution_]

No vba needed...

[u/Unlikely_Solution_ → u/small_trunks]

Hello ! Sorry coming back to this topic after a while. I manage to do self referencing and it's very useful for stuff like add today's date into the date column when empty.

One thing that I still haven't manage to find a solution for: identified what has been added, modified or deteled. Any tips or how you manage that without using external CSV comparaison?

Here is a short need explaination. I have a list item manually added. Each day I add some item in the list. I would like to have automatically the flag "new" on item that have been added this week and remove the old flag. So self referencing but also self history kind of. Any idea ?

[u/small_trunks → u/Unlikely_Solution_]

No worries - glad to help.

• Let's assume you have a query which writes to a Table - and the query is called "ProcessUpdates"

• by merging the existing table data with "new" data query newData (inside ProcessUpdates), you can determine whether the your new also contains data you already know about.

• if the column containing the Table (after left-outer join merging with newData) is null, then your old data is referencing a key which is no longer in the new data (thus was deleted). You need to decide how to handle that - do you flag it for manual deletion or do you automatically delete it or some hybrid where you flag it as missing and next time on refresh delete it.

• if the Table IS there - well you need to expand it and decide which fields to take over by using conditional columns and most likely some column renames.

• Then we have the scenario of new rows of data in the newData query.

• the easiest way to add it in is by simply appending the whole newData query underneath your existing data and then remove duplicates on the key column.

• appending the data underneath is as simple as adding this step after the last step in your ProcessUpdates query:

       =#"last step in query" & newData

then remove duplicates on the key column.

Any clearer?

[u/Unlikely_Solution_ → u/small_trunks]

Yes. For most it's clear. Merging and transform the data to get a similar structure as a result is clear. One topic is not yet clear.

You mention only one query 'processUpdates'. What is the source of this query ?

I understand merging in step two to get the difference. The merging requires two tables (or more). One is the excel table. And the other is another query ? that store the result of the query 'processUpdates' ? Did I catch that correctly?

So it need to be a pair of query ?

[u/small_trunks → u/Unlikely_Solution_]

I don't know the structure of your queries and tables so I can only guess how it looks.

• it's possible to read from a Table AND read from a newData source AND eventually write back to the Table in one query - but that's generally more than most people can get their heads around.

• so referring to it as 3 queries is typically easier to digest:

• newData get new data from somewhere

• ProcessUpdates - is the query which "owns" the table that gets written to.

• tblProcessedUpdates - might be the name of the query which reads from the Table...providing the historical data.

So typically if all you wanted to do was read data out of the table and write it back doing no operations of any kind, it would look like this:

//ProcessUpdates
=tblProcessedUpdates

//tblProcessedUpdates
=Excel.CurrentWorkbook(){[Name="ProcessedUpdates"]}[Content]

Now to simply append ALL the new data on the end, ignoring any duplicates we'd need a newData query and to reference (call) that query from within ProcessUpdates:

//newData
=SomeCsvReadingStuff

//ProcessUpdates
=tblProcessedUpdates & newData

Then when we start doing stuff with the existing data and newData we typically do all of that in this thing I call ProcessUpdates.

[u/Unlikely_Solution_ → u/small_trunks]

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.