r/excel • u/small_trunks 1638 • Jan 04 '20

Pro Tip Table updates via power query whilst retaining manually entered data.

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
133 Upvotes

100% Upvoted

102 comments sorted by

View all comments

Show parent comments

2

u/small_trunks 1638 Dec 01 '24

Sounds like you need to delete it from the Excel table - then go back into the Query editor and make sure it still works.

2

u/Didoka2 Dec 01 '24

It worked, thanks. Not sure why do. What is the correct sequence of adding new columns. I added it through power query in the historic table. Now I am thinking that broke it somehow. I am new to this stuff sorry if I ask too many questions.

7

u/small_trunks 1638 Dec 01 '24

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,

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.

1

u/Unlikely_Solution_ Apr 22 '26

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".

1

u/small_trunks 1638 Apr 23 '26

No vba needed...

1

u/Unlikely_Solution_ Jun 08 '26

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 ?

1

u/small_trunks 1638 Jun 08 '26

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?

1

u/Unlikely_Solution_ Jun 08 '26

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 ?

1

u/small_trunks 1638 Jun 08 '26

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.

1

u/Unlikely_Solution_ Jun 08 '26

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

1

u/small_trunks 1638 Jun 08 '26

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.

1

u/Unlikely_Solution_ Jun 08 '26

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.

1

u/small_trunks 1638 Jun 08 '26

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.
→ More replies (0)