I can't figure out how I would go about getting this query to work.
I have a collection of Excel files on SharePoint that I will be pulling data from. People will login to SP to amend the files and then I'll have a scheduled refresh to pull out the data.
There is only one version of each file (i.e. there is no week 1 file, week 2 file, etc.), SP version history is used if there is ever a need to see past files.
There is a weekly call to discuss the results. Ideally we want to be able to see the previous version of the result, however as they are overwriting the file Power BI won't be able to access any history without me amending how the files themselves work.
This is essentially what I want to achieve in the Query
Challenge I have is step 4 and 5.
I am taking data from file ABC.xlsx in week 2 I want to add that data to the existing data I've already taken from ABC.xlsx at the end of week 1. However Power BI will just overwrite the whole lot and I'll lose my existing data.
Seems like what you want is incremental refresh, which is only available in Premium.
Proud to be a Datanaut!