cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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. 


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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 910 guests
Please welcome our newest community members: