Archive old data to speed up SharePoint or OneDrive Folder refreshes in Power BI and Excel

Рет қаралды 12,064

Күн бұрын

🔽MORE INFO🔽
If you are using a SharePoint or OneDrive Folder as a data source for your Power BI or Excel reports then the more files you add the longer the refresh will take.
One solution is to periodically archive your older files into a consolidated table
I demonstrate 2 techniques.
1. Using bravo.bi/ to create a CSV consolidation
(you could also use the DAXStudio add in for Excel and follow a process similar to the one I demonstrate here... • Export Power Query Tab... )
2. Using dataflows
00:00 Intro
00:44 The Scenario
01:40 How to set up your consolidation initially with a few tips
06:55 The "Ideal" - use a database if you can
09:33 Using Bravo to generate a historic consolidation file
15:39 Using Dataflow to generate a historic consolidation file
22:10 Fixing up and refreshing a dataflow after making a mistake 😁
MyVideo on the use of SharePoint.Contents • The best way to connec...
Follow me via wyn.bio.link/
Comments are welcome..

Пікірлер: 63

@Dave-lc3cd 2 жыл бұрын

I'm definitely sharing this channel. Amazing practical, no nonsense content. Thank you for the fantastic tutorials!

@AccessAnalytic 2 жыл бұрын

Awesome, thanks Dave

@Lyriks_ Жыл бұрын

Wyn is probably the closest thing to an excel wizzard that I've ever came across, the guy literally does magic with a piece of software, well done.

@AccessAnalytic Жыл бұрын

Hah! Too kind Anthony ☺️

@fzm280579 2 жыл бұрын

This tip with the starting folder to reference on is huge!

@AccessAnalytic 2 жыл бұрын

Yeah that's a good one. Ken Puls (Skillwave) is a big proponent of that technique

@pvbri 4 ай бұрын

Very good solution, will definitely use the dataflows! Thanks!

@AccessAnalytic 4 ай бұрын

Cheers

@jasonswami9275 2 ай бұрын

Very good techniques, visited few times this video. i liked the sharepoint.content, split folder, exporting via bravo, importing archive file and appending query

@AccessAnalytic 2 ай бұрын

I appreciate you taking the time to let me know you found it useful

@fizexbakar1986 2 жыл бұрын

amazing video as always Wyn!

@AccessAnalytic 2 жыл бұрын

Thanks Fizek

@foodieguy4833 2 жыл бұрын

I've been using your videos in my work and the practical tips surely help! They are hard to find in the internet.

@AccessAnalytic 2 жыл бұрын

Cheers

@GrainneDuggan_Excel 2 жыл бұрын

Having an archive file would also help with the file and folder renaming issues when each FY is saved to its own folder system. Thanks for showing these various approaches! I have a fair few files I now need to review and tweak! Thank Wyn

@AccessAnalytic 2 жыл бұрын

You’re welcome Grainne. Thanks for the ongoing support

@sherlock27 2 жыл бұрын

Thanks Wyn, very helpful and detailed tutorial.

@AccessAnalytic 2 жыл бұрын

You’re welcome, thanks

@tigsik3128 2 жыл бұрын

I just found your channel and its very helpful! thank you for your content.

@AccessAnalytic 2 жыл бұрын

Thank you, glad it has helped

@vinhwizard 9 ай бұрын

Great, I learned much from this. Thank you so much

@AccessAnalytic 9 ай бұрын

You’re welcome

@wayneedmondson1065 2 жыл бұрын

Great information Wyn! Thanks for sharing. Thumbs up!!

@AccessAnalytic 2 жыл бұрын

Cheers Wayne

@KhalidWaleed59 Жыл бұрын

Thank for making such a valuable video, really like it. I had a similar problem, I have used the incremental refresh feature to address this. Since sharepoint isn't a foldable query source, we just need to add a Date/Time column before all the transformation and data collection step for it to work

@AccessAnalytic Жыл бұрын

Thanks Khalid. An incremental refresh option is on on my video "to-do" list. Good one.

@mohamedfouad7958 Жыл бұрын

Amazing, thanks a lot for your video. It helped me to resolve an issue I was facing to get data using sharepoint folders.

@AccessAnalytic Жыл бұрын

Fantastic to hear, thanks for taking the time to let me know

@alexj8117 Жыл бұрын

Just getting on board with dataflows now. Oh good, I'm only a year behind... Excellent video, as always.

@AccessAnalytic Жыл бұрын

Don't worry, you're at least 5 years ahead of most!

@imconfused1237 2 жыл бұрын

Great video as always Wyn! Easily my favourite Power BI / Query sub 😊 This is very timely, as I have a use case generating >25k rows of monthly data, with an archive requirement of 3yrs trend. Several constraints exist, primary being no SQL hence data source being SharePoint. I’m not sure whether DataFlow is enabled but if not, the CSV technique looks great. As you say, easy enough to perform that extraction in DAX Studio as well. Cheers Wyn and happy Easter! 🐣

@AccessAnalytic 2 жыл бұрын

Cheers for the support 😀

@baskis69 2 жыл бұрын

Thank you for sharing. As always very helpful¡¡¡¡

@AccessAnalytic 2 жыл бұрын

You’re welcome arnau, thanks for the kind comment

@khaledchowdhury1285 2 жыл бұрын

We do that even with Oracle as we have over millions of records and historical years are slow. So we have one that refreshes daily, history parameters are updated once every quarter. So, this is pretty neat trick even beyond SharePoint. Also, if you leverage premium everything is done and ready in one place

@AccessAnalytic 2 жыл бұрын

Good to know Khaled

@kassiowifried 2 жыл бұрын

Thank you

@AccessAnalytic 2 жыл бұрын

You're welcome

@UlyssesHaq 2 жыл бұрын

Tremendous again Wyn. Diolch yn fawr always. Question: the CSV download of tables with Bravo. Does that export the table as it appears in PowerQuery, or is it the table from the model, PowerBI front end, including any calculated columns etc?

@AccessAnalytic 2 жыл бұрын

Diolch, it will be the data model and pretty sure it will include calculated columns.

@wmfexcel 2 жыл бұрын

This is to echo your approach. As i did that in my work and it saved really lots of refreshing time. (there are 100+ files with more than 10M rows of records). Ideally, incremental refresh should be the best way in my opinion... however, I just do have that in my workspace... :( So this technique is a good alternative in real life.

@AccessAnalytic 2 жыл бұрын

Thanks for the confirmation

@wmfexcel 2 жыл бұрын

@@AccessAnalytic just to add one point. It works well when original data files are Excel. If original data files are already CSV, it won’t make a big difference (at least in my case).

@AccessAnalytic 2 жыл бұрын

@@wmfexcel OK, thanks

@osielgutierrez7837 2 жыл бұрын

hello Wyn, do you get the same performance if you do it from onedrive?. Thaks

@AccessAnalytic 2 жыл бұрын

Hi Osiel, you should do. One Drive is your personal SharePoint

@mabenba 2 жыл бұрын

This is amazing. I am having the exact same problem as you stated. Receiving several files every day, it takes almost 20 minutes to refresh the data as the project is growin larger. Thank you so much for sharing! Amazing the data flow solution!! Is there a way to automate the process? for example to take the last five files in the desktop and everything but the last 5 in the data flows?

@AccessAnalytic 2 жыл бұрын

Thanks, you could potentially filter on modified date, it’s a bit risky though, plus you’d still need to refresh the dataflow each time so not really worth it ( I don’t think). I’d just set up a separate folder for the data flow source and put the files in there occasionally and refresh. You could use power automate to trigger the data flow refresh as soon as files are added into the folder

@mabenba 2 жыл бұрын

@@AccessAnalytic thanks for your response. I was thinking about using power automate to make the transition between folders every ten days or so. I made a copy of my desktop file, given the fact that I have too many queries to copy, and with this copied pbix, I made the same think you made in dataflows but with desktop and publishing the new dataset. Thanks for the inspiration!

@AccessAnalytic 2 жыл бұрын

No worries

@Folisaa Жыл бұрын

What a great video! The first approach, appending historical data in PQ. When working with large datasets, it still takes a long time to load that historical data with every refresh (we disable it in it's own query, but it's being pulled through appended table). Is there a way to load historical data once, and then disable it for future refreshes? Maybe skipping Appending part would help, but then table with historical/current data is split into two tables...

@AccessAnalytic Жыл бұрын

With files / folders it will always pull all data in each time. Gilbert has a blog article that I’ve never tried out www.fourmoo.com/2020/06/10/how-you-can-incrementally-refresh-any-power-bi-data-source-this-example-is-a-csv-file/

@deepakagrawal465 Жыл бұрын

@@AccessAnalytic If it is pulling all data each time then how it is doing it quickly vs one single query ? I have also faced this problem with appending queries but it did not solve the issue of heavy refresh time. Using append with DAX is the solution I ended up doing after loading both the queries in the model and disabling refresh on historical data query. Would be really interesting to know how your method is reducing the refresh time and if it will be applicable to large data files as well using append query method ?

@AccessAnalytic Жыл бұрын

I’ve not tried Gilbert’s approach so I’m not sure. DAX append sounds an interesting approach

@deepakagrawal465 Жыл бұрын

@@AccessAnalytic We have been using DAX append for several years now. Though it bloats the model a bit but refreshes are lightening fast....

@chenyvonne8205 Жыл бұрын

Could you please share how to resolve "combine files --didn't recognize the format"? My files are all xlsx but it failed to upload in Power BI Query

@AccessAnalytic Жыл бұрын

I’d suggest posting some screenshots and explanations here aka.ms/excelcommunity for some help on that

@skysenses Жыл бұрын

it throws up error when consolidating sheets. Error - we didn't recognize the format of the first file(). Please filter the list of files so that it contains supported type. However if we use the files instead of content, this works. Any suggestion?

@AccessAnalytic Жыл бұрын

Not come across that. Is it purely .xlsx files in the folder?

@ivancuadros8731 Жыл бұрын

I just saw that in minute 4:08 some transformations are done, but it look you edited because it does not show how you handled the custom function. In my case I do a lot of transformations before is ready to combine tables.

@AccessAnalytic Жыл бұрын

Hi, no editing, the helper functions get auto created. Take a look at my explanation here : kzfaq.info/get/bejne/pLacpbSGx6vSZKM.html