I've created a solution for ETL using Excel Power Query. The solution pulls in data from various sources and does the transform steps and finally uses Append to create an output dataset, which I can then plan to upload to a database. As this is a routine task, I'd like to add an index column to the "output" dataset, which is still easy, however, I need to somehow keep track of the last index number (for any batch) so that my next run of the solution uses the last index from the previous one and continues from there. The end game here is to fully automate the process so I don't have to manually feed the next index. I hope this is a clear explanation.
Perhaps you can create an additional query that pulls the max index from the table and re-use that same query to calculate the index column? I know this creates a circular reference in the queries, but maybe it works?