Using Excel PowerQuery, is there a way to load a query to the original table?

Question

I want to use PowerQuery to read in a table of raw transactions (in a csv file) and combine it with a table of running transactions. The hitch is that the running transactions table contains manually-create labels.

For example, runningTable could be

Date	Amount	Notes
2020-01-01	5.52	Some notes
2020-01-02	-4.25	Other notes

The Notes column is manually entered by the user to label the context for that transaction.

rawTable could be | Date | Amount | | -------- | -------------- | | 2020-01-01 | 5.52 | | 2020-01-02 | -4.25 | | 2020-01-03 | 10.00 | | 2020-01-04 | 12.00 |

Since there are more dates in rawTable, I need to update runningTable to include the latest data. Currently, using PowerQuery, I can pretty easily read in both tables, do some transformations and filters, append one to the other, and output a new runningTable2 with all the notes from runningTable, like so:

Date	Amount	Notes
2020-01-01	5.52	Some notes
2020-01-02	-4.25	Other notes
2020-01-03	10.00
2020-01-04	12.00

The problem is that I want runningTable itself to be updated since edits in runningTable2 just get over-written when I run the query again. The best I can manage at the moment is to manually copy and paste runningTable2 onto runningTable, thereby replacing that input table.

But is there a way to do this automatically in Excel?

Answer 1

Ricard Diaz's comment provided the term that was missing from my vocabulary (i.e., "self reference tables"). It was exactly what I needed to find a solution.

https://exceleratorbi.com.au/self-referencing-tables-power-query/

Basically, you read in the table without the Other notes column and do your transformations etc., then you read in the output table again merging in the Other notes column. The "key" here is that you need a key or index column to properly merge in the results. I already had one though I didn't mention it in the question.