Faster SharePoint folder consolidation using Incremental Refresh (see warning in the notes)

⚡⚠️ Enabling incremental refresh means you will not be able to download the file from the service, so keep hold of that PBI desktop copy ⚠️⚡

Are you adding more and more files every day / week / month to your SharePoint or OneDrive folder and using Power BI to consolidate them? Is your refresh taking long and longer?

What if you could just add the latest file rather than re-importing ALL files every time....

This video shows how to speed up your refresh when pulling data from a SharePoint or OneDrive folder

00:00 Intro
02:54 Turning your filename into a datetime column
04:38 Adding RangeStart and RangeEnd parameters
06:15 Filtering the datetime column for the parameters
08:30 Setting up the incremental refresh time windows
11:15 Testing it out
13:32 The Result

Article by DataLineo
and some extra info on partitions and dataflows

My previous videos explaining From Folder:

Basic:

Debugging tip

Using a File Path (and including sub folders)

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[AccessAnalytic]

⚡⚡ Enabling incremental refresh means you will not be able to download the file from the service, so keep hold of that PBI desktop copy ⚡⚡

Always take a look at the description for these sorts of updates

[zzota]

This is just what I need. I thought Incremental Refresh was only applicable to SQL datasets, so this is brilliant. Thanks Wyn 🙂

[mkcorrea]

Pretty nice!
Some really big data we can also segment by generating files with that segmentation and naming it for filtering when consolidating from the SharePoint folder.
That will enable solving a demand I have at my company. Thank you so much!👏🏻👏🏻👏🏻

[data-made-simple]

Great timing - I’m up to 30 mins refresh time on one setup and been putting off looking into incremental refreshes. Explained clearly and simply as usual.

Thanks for your videos- it’s really helped using SharePoint folders as quick non code approach over alternatives.

[jawadahmadehssan]

I went from 6 min to 8 seconds. Thank you. Can we set up a something similar for data flows?

[mjb]

Is this also available for Excel PQ, or just Power BI?

[JorgePerez-buph]

Great video as always. I got confused about something: The "RangeEnd" parameter... Am I supposed to change it manually for the next update or will it automatically update to the last date found in my data files? How does it work? I want to understand if this RangeEnd parameter is something I have to maintain, or if once the incremental refresh is set I can forget about it? Thanks!

[aigbekennethomorodion]

Does incremental refresh now work on data sources that don’t support query folding? Just curious or maybe I have missed something in any new Microsoft updates.

[SergiyVakshul]

Wyn, I’d like to add that if a file name points to the future date, then this file won’t be processed during the refresh time. Another important point is what is considered to be the current date. I mean that the specified time zone for the scheduled refresh can shift your date one day, which may impact the result (the docs cover this aspect).

[kiasca]

Thanks really helpful, is it possible to do it on power query excel, so it doesn't load again the data when refreshing power pivot?

[suheilsamara]

This is Brilliant!
Issue is that my file names do not include indication for the date thus the options I have is either to use the date created or date modified columns. What are the concerns in such approach ? I rather think that using the date modified in safer than file name because if I rely on the file name while the content is modified for any reason, such changes won't be picked by the incremental refresh. Agree!

[StephenBrincat]

Excellent, one question would datetime like 20/08/2024 00:00:00 work? I mean time will be always midnight.

[zorankrekic]

Simple and nice when I have new file, what to do when new data are append on last file, and is possible incremental refresh set into dataflow

[LukasMissias]

Excellent video! Is it possible to apply to Sharepoint lists?

[woliveiras]

Amazing video. Thank you. Incremental refresh in PowerBI is crazy. How Microsoft can make something simple becomes a hard thing.
To me it is not clear what is the best parameter to choose if I wanna add just the current day in my dataset.

And why I would update the entire last month(for example) if this is an incremental refresh. I already added it in my previous update. Do you understand my point?

[munawarhussain]

I have completed the setup. However when I publish a file with increamental refresh and then I goto schedule refresh it show error in refresh. Error is "column Debit is not available" although column is available in dataset and there is no error in BI Desktop . However when I turn off increamental refresh then publishit again, then there is no such error. Any Idea how I can solve this?

[okmr]

Hi Wyn, I have 2 qutestions:
1. What happens if current time is beyond RangeEnd? Should I adjust the value of RangeEnd to a future DateTime?
2. I guess this is only work for "consolidateing Excel files" on SharePoint Folder NOT for importing individul Excle file by Web?

Thank you Wyn, great video again!

[AshishSingh-twvd]

Great content sir ❤ The only problem which i am facing whith this is the dates . It would have been much better if you could have just explained the purpose and roles of all three dates(one which is parametrized, archive data starting and incrementally refresh date) as well.

[shidubravinarunasalam]

Hi is this option available in Power BI Desktop-On Premise, i couldn't find this option while right clicking on the table, i have done this following steps, what you have done, but the option is not available in Power BI Desktop-On Premise, but if i open in Power Bi Desktop Cloud i can do this process, but again during uploading the huge file to cloud not possible, since i have Power BI Pro License, any other suggestion from your side?

[iiiiii-wh]

please can you show us this for dstaflows? I'm pretty sure I have a PPU licence assigned by my company but I've never used any of it's capabilities