Continuously Append New Data to sheet

[MHau5]

I have multiple excel sheets that will be used every day by a different user (all the same columns, etc). At the end of every day I'd like to have all the data in those sheets uploaded into a "master" excel sheet without overwriting the data that was imported from the previous day. There's a date column in each of the user based excel sheet that I'll be able to use to go back and check work for on a specified date after it's imported into the "master" file.

I've been experimenting with Power Query but can't seem to connect the dots on how to make this work.

Any help would be appreciated.

 

[GraH]

Maybe you can try with this procedure at the core...

1. You append for the first time and load the result in a table.
2. You now import this table again in a new query (temporarily to get the code you need, so bear with please)
3. Open the advanced editor and copy the code from this temp query (source step)
4. Open the original query code also via the advanced editor
5. Insert the copied code before the original Source step of this query
6. Now rename the first "Source" step f.e. "Historized", because you can't have two steps with the same name.
7. Insert a new step "Combine_with_original" at the end of this query
8. and use the formula = Table.Combine ({#"Previous Step", "Historized"})
9. Update the code after the in statement: replace whatever is there with "Combine_with_original"
10. Delete the temp query.
11. Save and load.

Be aware you probably need to insert a few new steps or even a referenced query, so you can:

1. Identify the last records you already loaded. Without more details it almost impossible to provide guidance. And maybe it is not even possible.
2. filter out these already processed records, so you do not append them again.
3. Often 1+2 can be done when there is a date available in the data.
4. Alternatively is doing a left anti between the appending all data with the historized data prior to doing the merge