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
Highlighted
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. 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 97 members 1,562 guests
Please welcome our newest community members: