Excel Power Query - Incremental Load from Query and adding date

Question

I am trying to do something quite simple which I am failing to understand. Take the output from a query, date time stamp and write it into a Excel table. Iterate the logic again and you get the same output but the generated date time has progressed in time.

Query 1 -- From SQL which yields 2 columns category, count. I am taking this and adding a generated date to it using DateTime.LocalNow().

Query 2 -- Target table

How can i construct a query which adds to an existing table and doesnt require me to load the result into a new table.

I have seen this blog.oraylis.de and i cant make it work since the DateTime.LocalNow() call runs for source and target and i end up with the same datetime throughout the query.

I think i am missing something obvious.

EDIT:-

= Table.Combine({SOURCE_DATA, TARGET_DATA})

This loads into a 3rd new table and doesnt take into account that 3rd table when loading - so you just end up with a new version of just the first two tables with new timestamp

Answer 1

These steps should work

1. create a query Q1 based on the SQL Statement, add your timestamp using DateTime.LocalNow() and load this into an Excel table (execute the query)

2. create a new query Q2 based on this Excel new table (just like that, no transforms)

3. Modify the first query Q1 by adding the Table.Combine with Q2 as the last step.

So, in other words, Q2 loads the existing data from the Excel table into which Q1 writes. The Excel table is always written completely but since the existing data is preserved you will get the result of new data being loaded to the table. Hope this helps. Good luck, Hilmar