The best way to connect to a SharePoint Folder to speed up your Excel and Power BI data refresh

How to consolidate multiple files from a SharePoint folder using Power Query in Excel and Power BI.
🔽 More Info below PLUS warning 🔽
Warning: to use SharePoint.Contents the user must hace access to to all levels of the SharePoint site.
Additional technique to speed up refresh using incremental refresh
• Faster SharePoint fold...
Sadly the button is not included in 365 for Business . However you can still use the code
= SharePoint.Contents("YOURPATH/", [ApiVersion = 15])
Video I mention explaining the consolidation helper queries
• Combining Multiple Fil...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@Fonzyrr]

This video is a genuine service to humanity

[@AccessAnalytic]

😂 - made me smile - thanks!

[@robm1010]

I agree 1000%. This video is gold and is something I never want to lose.

[@AccessAnalytic]

Thank you 😀

[@Fonzyrr]

@Access Analytic question, have you ever set up a "SharePoint" Server? Meaning you have a SharePoint dedicated to running queries that are aggregating from forms you created for your company on another SharePoint site. You can then query to this "SharePoint Server" site to get reports from?

[@arisaleh1980]

I am just speechless. What an unbelivably timesaving workaround.

[@juanramon8652]

Even in 2024 this Video still makes me say : you are the Master !!!

[@AccessAnalytic]

Cheers Juan!

[@jamieashton660]

To be honest this video has just paid for my year's KZbin subscription. Can you imagine what the world would be like if every video was this informative. I had practically given up on trying to get files out of Shitepoint until now.

[@AccessAnalytic]

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

[@marcoslopez9997]

How can I like this video many more times? Thank you for it!

[@AccessAnalytic]

😆Thank you!

[@joaovaltergirardi9709]

You are the only person who came with a decent solution that really works. Thanks!

[@AccessAnalytic]

You're welcome

[@agnieszkabartoszewicz7163]

Thank you! That was exactly what I was looking for! Finally I managed to connect with my subfolder on the Sharepoint.

[@AccessAnalytic]

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

[@KonstantinAushev]

Finally! It was so hard to find such elegant solution to the problem of slow working with SPO folders...

[@AccessAnalytic]

I’m glad you found it useful.

[@markporiss4411]

Omg, finally found this! Agree, this is a genuine service to humanity

[@AccessAnalytic]

Hah 😆, thank you

[@seanwood]

This is EXTREMELY helpful. Thanks very much for making this video.

[@AccessAnalytic]

No worries. Thanks for taking the time to leave a kind comment Sean

[@chinedumjoseph9875]

You have saved my life! I wish I could give you a million likes. Thanks a lot for your clear and detailed explanation. Well done!

[@AccessAnalytic]

Glad to help Chinedum, thanks for taking the time to let me know it helped you. Hopefully you’ll let others know about this channel 😀

[@nickvanmaele8059]

Great video, and nifty workarounds. Thanks for sharing!

[@AccessAnalytic]

Thanks Nick

[@user-gd2bd7gh5o]

Thank you for posting a better/quicker way to connect to sharepoint folder! It worked very well for me!

[@AccessAnalytic]

You’re welcome

[@EdSongoku]

Great video! You explained this to me in comments of another video of yours not too long ago, saved my life 😁

[@AccessAnalytic]

Glad to have helped Eddie. I’ve had similar questions multiple times recently so it was overdue for a video 😀

[@paser2]

Absolutely brilliant! Thank you so much. Finding subfolders in SP has been a killer for me until now. Much appreciated.

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@wayneedmondson1065]

Thanks Wyn! Always useful and interesting tips. Thumbs up!!

[@AccessAnalytic]

Thanks Wayne

[@neilthackeray181]

MY GOD!!! Amazing! spent ages getting errors using other methods, love the simple exceptionally clear step by step presentation - Thank you!

[@AccessAnalytic]

You’re welcome Neil. Glad it helped and thanks for taking the time to leave a kind comment

[@djaurit]

Agree with so many other comments! Thank you for sharing your knowledge in such a clear way! Many thanks!

[@AccessAnalytic]

You’re very welcome. Thanks for taking the time to leave a kind comment

[@rick_.]

I always learn something new from your videos!

[@AccessAnalytic]

Thanks for letting me know 😀

[@idafisker6645]

This video helped me save many hours of waiting! Thanks!

[@AccessAnalytic]

You’re welcome Ida. Thanks for letting me know it helped

[@bradfry6396]

Excellent Wyn, I’ll be using this for sure 👍

[@AccessAnalytic]

Cheers Brad

[@AmritaOSullivan]

This is brilliant. And explained so logically!!!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment Amrita

[@TheMetaModel]

Thank you, thank you, thank you for making this video, so incredibly helpful!

[@AccessAnalytic]

You’re welcome

[@JuanIArana]

Thanks for sharing Wyn. Very useful content in this vídeo!

[@AccessAnalytic]

Thanks 😀

[@bjrnkrogh9768]

Holy c... - the speed on this solution is Amazing !!!! Instead of 5 minutes - I'm getting data in 30 seconds - Thank you very much !!!!!

[@AccessAnalytic]

You’re very welcome and thanks for confirming the speed gains

[@julie_chen]

Thank you. I have used your teaching from this video today.

[@AccessAnalytic]

Thanks for taking the time to let me know it helped Julie

[@elyseweldon8582]

This was incredibly helpful, thank you so much!

[@AccessAnalytic]

Glad to help Elyse, thanks for taking the time to let me know it was useful

[@joshuabradshaw1647]

Second time this has saved me at work. Thank you, Sir!!

[@AccessAnalytic]

Glad to help Joshua 😀

[@dbalmo]

Excelent! Clear and concise!

[@AccessAnalytic]

Thanks 😃

[@pranjalagrawal4167]

Worked like a charm. Thank you sir.

[@AccessAnalytic]

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

[@jimbeverley]

What an awesome hack! I can't believe Microsoft hasn't already baked the SharePoint.Contents functionality into their PQ menus. Thank you!!!

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@yordanosadigo]

I LOVE YOUR LESSONS ..PRACTICAL

[@AccessAnalytic]

Thank you

[@user-kb6fz4xl8j]

Thank you so much for the detailed tutorial! Your explanation makes it easy to understand!

[@AccessAnalytic]

You’re welcome

[@Girhofamily]

Thanks for solving puzzles for me. Love you man. You are great 👍

[@AccessAnalytic]

Too kind ☺️

[@hungrykiwi38]

Great help, thanks a lot!

[@AccessAnalytic]

Glad it helped!

[@Matheus-tr9jf]

Really, thanks for sharing your knowledge. You saved my day here. Was trying for 2 hours how to link it. The Power BI/EXCEL in app information is horrible for this. This video/tutorial deserves an OSCAR.

[@AccessAnalytic]

😀 thanks Matheus

[@stevevuoso8411]

Amazing stuff! Thank you.

[@AccessAnalytic]

Thanks Steve

[@ashishshrestha7179]

Sharepoint.Contents() saved my tons of data loading time.. Thanks a lot..

[@AccessAnalytic]

Great to know, thanks for taking the time to leave a comment

[@julianmitchell3914]

Excellent video and clearly explained. Thank you

[@AccessAnalytic]

You’re welcome. Thanks for taking the time to leave a kind comment

[@beabeeeee]

I just want to say that your video has helped me. thank you

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment Bea

[@cristian.angyal]

Great video! Learned something new! Thank you!

[@AccessAnalytic]

Cheers for the support Cristian

[@DanBrill]

Awesome way to do it. Thanks so much for sharing it. This will be a great way for our team to share data easily.

[@AccessAnalytic]

Glad to help Dan

[@TheWeteyezz]

Freaking Awesome! Thank you. As a beginner this is extremely helpful

[@AccessAnalytic]

You’re welcome

[@zzota]

Very useful. I'll be updating my steps tomorrow 😄

[@AccessAnalytic]

That’s great to know, thanks for commenting! 😀

[@dganalysis]

Great video - well paced explanation.

[@AccessAnalytic]

Thanks

[@davidfamilydoctor9430]

"Move to beginning" - Such a time saver

[@AccessAnalytic]

Thanks 😀 All these little things help

[@agoes9983]

Great..., this is what i'm looking for, Thank you

[@AccessAnalytic]

Glad to help

[@alexkim7270]

Wow. This is a neat workaround. Thanks for sharing this! I would generally avoid this method because the "drill down" isn't expanded and my worry is it isn't shown in Applied Steps. I guess by breaking them like what you showed here does the trick! So now whenever the folder path changes in future, I can go to that step and edit!

[@AccessAnalytic]

Absolutely Alex, I used to feel the same way

[@twiggyinc1]

I wish I could give a x100 thumbs up. So useful for my job. Thanks

[@AccessAnalytic]

Awesome!

[@balrajvirdee1087]

Brilliant, just brilliant thanks so much 👍🏽👍🏽

[@AccessAnalytic]

☺️. Thank you

[@shafiqr2000]

Very useful video. Thanks a lot Sir!

[@AccessAnalytic]

You’re welcome Shafiq

[@helenloft8484]

Thank You!!!!! This is so helpful

[@AccessAnalytic]

You’re welcome 😀

[@jorstube]

Useful Wyn!!!!

[@AccessAnalytic]

Cheers Jorge

[@danieviljoen5911]

Cool, liked this one. Helped me a lot

[@AccessAnalytic]

Great to know, thanks

[@justinwduff]

Game changer. Thank you so much!!!

[@AccessAnalytic]

You’re welcome

[@stu4321]

Thanks for sharing!

[@AccessAnalytic]

You're welcome

[@bibhutisen3197]

Great video ❤. Thank you.

[@AccessAnalytic]

You're welcome

[@user-we3nk8dt4g]

Love the column width hack!

[@AccessAnalytic]

Thanks, it turns out you can just double click on the edge !!

[@user-ly6rx4sc4j]

Thank you so much tha was so helpful i solve my problem , love you man

[@AccessAnalytic]

☺️ glad I could help 🤘🏼

[@tigere3528]

Man!! Lifesaver - I was using previous method - in our company we have a preservation hold folder that is bloated, so it was taking forever to load a file from one of the other folders because it has to first load all data from the other folders before I can filter for what I want

[@AccessAnalytic]

Excellent!

[@syedabu7685]

Awesome contribution. you saved my tons of hours. Thank you soo much

[@AccessAnalytic]

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

[@felipesignorellireis7839]

Save my life. First time using sharepoint folder.

[@AccessAnalytic]

Excellent .. glad to help Felipe

[@scottrels8251]

well presented, clear and knowledgeable tutorial

[@AccessAnalytic]

Thank you

[@monicacisneros327]

It was easy to follow. I feel successful after doing this.

[@AccessAnalytic]

I’m glad you found it useful

[@nhuluong4005]

your video helps me save a lot of time.... thank you so much

[@AccessAnalytic]

Happy to help

[@farizg]

Awesome! Thank you!

[@AccessAnalytic]

You’re welcome

[@Belakavadi]

Awesome trick. You saved my day.

[@AccessAnalytic]

You're welcome 😄

[@mih_a]

Thank you mate!

[@AccessAnalytic]

You’re welcome Alex

[@Girhofamily]

Best video I found so far on KZbin

[@AccessAnalytic]

Thanks for letting me know 😀

[@superlils100]

I've been trying to solve performance issues for a week!! I'd LOVE this video if I could

[@AccessAnalytic]

Glad to help, thanks for taking the time to leave a kind comment

[@paulskelton8799]

Thank you so much for sharing this took some finding and several other methods are not so effective

[@AccessAnalytic]

You’re welcome

[@mounahamtine6945]

Incredible, thank you so much!!!!

[@AccessAnalytic]

You’re welcome

[@dhikisatriawan5794]

Amazing! This is what I want. You saved my lifee haha

[@AccessAnalytic]

Glad to have helped 😀

[@AllendePerez]

you are now my hero! Thank you

[@AccessAnalytic]

☺️ Glad to help.

[@ivanvelasco3118]

It was so useful! Thanks!!!

[@AccessAnalytic]

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

[@IanDangerfield]

Bless you sir saved me a lot of headache

[@AccessAnalytic]

You’re welcome Ian

[@thuytamdang3390]

Thank a lot for sharing. extremely useful content.

[@AccessAnalytic]

You’re welcome Thuy Tam Dang

[@AccessAnalytic]

You’re welcome Thuy Tam Dang

[@pranaysharma8694]

Awesome- thanks a lot!

[@AccessAnalytic]

You’re welcome

[@thomasarandt5677]

Just awesome! Ultimate trick!

[@AccessAnalytic]

Cheers Thomas

[@MicaelaJoieB.Fajardo]

This helps! Thank you!

[@AccessAnalytic]

You’re welcome

[@hungpq2676]

Thank you so much sir, this video help me a lot in my job. I didn't know what to do with SharePoint folder when my boos asked.

[@AccessAnalytic]

You are welcome. I appreciate you taking the time to let me know you found it useful

[@luzkrg]

THANK YOU, THANK YOU, THANK YOU!!! 😃

[@AccessAnalytic]

You’re welcome Luz

[@alexfruitservice8380]

Thanky you very much!

[@AccessAnalytic]

You’re welcome

[@Mogiron10]

Wow!!! This is Awesome! Your going to save me hours now!

[@AccessAnalytic]

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

[@xjuliussx]

Great video, put's light in a very complicated mechanism to access excel files. once again this proves how bad this non-feature was implemented from PowerBI point of view. The casual player will never figure out this. It has no explanation why power bi developers and sharepoint developers didn't manage to find a simple and easy way to access each specific folder !

[@AccessAnalytic]

Agreed. In the next 6 months I expect to see the dataflow UI hit power bi desktop and that has a better connector experience

[@frankwestphal2883]

Thanks for sharing. You made my day. With standard method it is a pain if a SharePoint has 10000s of files....

[@AccessAnalytic]

Totally Frank. Glad to help

[@chyntiaoktavia7158]

thank you so much!!! I wish you get good & blessing karma because of your very useful knowledge!!

[@AccessAnalytic]

Thank you

[@andrewmackenzie2638]

This is so much cleaner than my "filter 'ends with'" hack Also seems to load waaaay faster, which is my biggest gripe with PowerX and SharePoint

[@AccessAnalytic]

Yes, definitely quicker as t doesn't have to navigate all that meta-data

[@matsiengboyz]

Amazing, thank you!

[@AccessAnalytic]

You're welcome

[@nickvanmaele8059]

Hi Wynn. The SharePoint.Contents( ) approach works really well. For private channels in Teams, at first sight it looks as if SharePoint.Contents( ) does not return the private channel. That is because a private channel actually has a different SharePoint URL, so the first step Source will have to point to another URL.

[@AccessAnalytic]

Good pickup Nick

[@Misterbigpocket]

Thanks sifu..u help me alot..

[@AccessAnalytic]

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

[@FlatEarthTheory]

Very helpful

[@AccessAnalytic]

You're welcome Max

[@manishkotecha9476]

Thank you so much - amazing

[@AccessAnalytic]

Thanks, I appreciate the comment

[@darrenvinales419]

Thanks very much

[@AccessAnalytic]

No worries

[@ryansymons724]

Brilliant!!!

[@AccessAnalytic]

Thanks 😀

[@yoniso2006]

This video very useful 😍

[@AccessAnalytic]

Thanks!

[@okmr7706]

Very good video, helped me on import data from Excel and csv files hosted on Sharepoint folder. Appreciate it very much. However, this video is misleading to me as the formats of my Excel and csv files are different so do need to be consolidated. So I suggest you change the title to add "consolidate" in it as quite some contents in this video is about "consolid" your 3 excel files. Again, thank you for your video, expecially the tricks in it.

[@AccessAnalytic]

If you’re connecting to individual files I’d recommend kzbin.info/www/bejne/rIG5Z2qIgayihcksi=whWP7aSIWmeKw_4x

Very helpful video! thank you

[@AccessAnalytic]

You’re welcome

[@wyatt_cole]

Bless you

[@vincentcox9212]

Really useful

[@AccessAnalytic]

Thanks

[@user-bx2qw9sb3b]

Mil Gracias 🙏🙏

[@AccessAnalytic]

No worries