cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Veles Member
Member

Add data to dataset on refresh rather than replace.

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

 

 

  1. Refresh schedule triggered
  2. Query takes data from all files in the folder
  3. Add on a custom column based on the current date to give the week number
  4. If this week doesn't exist in the dataset yet, add it on to the existing data
  5. If this week does exist, overwrite the existing data for that week only

 

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.

1 REPLY 1
Super User
Super User

Re: Add data to dataset on refresh rather than replace.

Seems like what you want is incremental refresh, which is only available in Premium. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!