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

Рет қаралды 192,165

2 жыл бұрын

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/

Пікірлер: 584

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

@jamieashton660 4 ай бұрын

To be honest this video has just paid for my year's KZfaq 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 4 ай бұрын

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

@juanramon8652 Ай бұрын

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

@AccessAnalytic Ай бұрын

Cheers Juan!

@joaovaltergirardi9709 11 ай бұрын

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

@AccessAnalytic 11 ай бұрын

You're welcome

@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

@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

@chinedumjoseph9875 2 жыл бұрын

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 2 жыл бұрын

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 😀

@alexkim7270 2 жыл бұрын

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 2 жыл бұрын

Absolutely Alex, I used to feel the same way

@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

@EdSongoku 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

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

@KonstantinAushev 5 ай бұрын

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

@AccessAnalytic 5 ай бұрын

I’m glad you found it useful.

@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

@seanwood 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

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

@nickvanmaele8059 Жыл бұрын

Great video, and nifty workarounds. Thanks for sharing!

@AccessAnalytic Жыл бұрын

Thanks Nick

@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!

@wayneedmondson1065 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

Thanks Wayne

@markporiss4411 5 ай бұрын

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

@AccessAnalytic 5 ай бұрын

Hah 😆, thank you

@AmritaOSullivan Жыл бұрын

This is brilliant. And explained so logically!!!

@AccessAnalytic Жыл бұрын

Thanks for taking the time to leave a kind comment Amrita

@djaurit 4 ай бұрын

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

@AccessAnalytic 4 ай бұрын

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

@JuanIArana 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

Thanks 😀

@bradfry6396 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

Cheers Brad

@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

@user-gd2bd7gh5o 2 ай бұрын

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

@AccessAnalytic 2 ай бұрын

You’re welcome

@julianmitchell3914 Жыл бұрын

Excellent video and clearly explained. Thank you

@AccessAnalytic Жыл бұрын

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

@elyseweldon8582 2 жыл бұрын

This was incredibly helpful, thank you so much!

@AccessAnalytic 2 жыл бұрын

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

@dbalmo Жыл бұрын

Excelent! Clear and concise!

@AccessAnalytic Жыл бұрын

Thanks 😃

@rick_. 2 жыл бұрын

I always learn something new from your videos!

@AccessAnalytic 2 жыл бұрын

Thanks for letting me know 😀

@TheMetaModel Жыл бұрын

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

@AccessAnalytic Жыл бұрын

You’re welcome

@idafisker6645 Жыл бұрын

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

@AccessAnalytic Жыл бұрын

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

@agnieszkabartoszewicz7163 25 күн бұрын

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

@AccessAnalytic 25 күн бұрын

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

@yordanosadigo Жыл бұрын

I LOVE YOUR LESSONS ..PRACTICAL

@AccessAnalytic Жыл бұрын

Thank you

@Girhofamily 9 ай бұрын

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

@AccessAnalytic 9 ай бұрын

Too kind ☺️

@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

@marcoslopez9997 4 күн бұрын

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

@AccessAnalytic 4 күн бұрын

😆Thank you!

@hungrykiwi38 Жыл бұрын

Great help, thanks a lot!

@AccessAnalytic Жыл бұрын

Glad it helped!

@dganalysis Жыл бұрын

Great video - well paced explanation.

@AccessAnalytic Жыл бұрын

Thanks

@user-kb6fz4xl8j 6 ай бұрын

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

@AccessAnalytic 6 ай бұрын

You’re welcome

@cristian.angyal 2 жыл бұрын

Great video! Learned something new! Thank you!

@AccessAnalytic 2 жыл бұрын

Cheers for the support Cristian

@superlils100 10 ай бұрын

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

@AccessAnalytic 10 ай бұрын

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

@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

@stevevuoso8411 Жыл бұрын

Amazing stuff! Thank you.

@AccessAnalytic Жыл бұрын

Thanks Steve

@zzota 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

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

@TheWeteyezz Жыл бұрын

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

@AccessAnalytic Жыл бұрын

You’re welcome

@joshuabradshaw1647 Жыл бұрын

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

@AccessAnalytic Жыл бұрын

Glad to help Joshua 😀

@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

@shafiqr2000 Жыл бұрын

Very useful video. Thanks a lot Sir!

@AccessAnalytic Жыл бұрын

You’re welcome Shafiq

@jimbeverley 8 ай бұрын

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

@AccessAnalytic 8 ай бұрын

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

@justinwduff 8 ай бұрын

Game changer. Thank you so much!!!

@AccessAnalytic 8 ай бұрын

You’re welcome

@danieviljoen5911 8 ай бұрын

Cool, liked this one. Helped me a lot

@AccessAnalytic 8 ай бұрын

Great to know, thanks

@scottrels8251 Жыл бұрын

well presented, clear and knowledgeable tutorial

@AccessAnalytic Жыл бұрын

Thank you

@helenloft8484 Жыл бұрын

Thank You!!!!! This is so helpful

@AccessAnalytic Жыл бұрын

You’re welcome 😀

@balrajvirdee1087 Жыл бұрын

Brilliant, just brilliant thanks so much 👍🏽👍🏽

@AccessAnalytic Жыл бұрын

☺️. Thank you

@thomasarandt5677 2 жыл бұрын

Just awesome! Ultimate trick!

@AccessAnalytic 2 жыл бұрын

Cheers Thomas

@xjuliussx 9 ай бұрын

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 9 ай бұрын

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

@agoes9983 Жыл бұрын

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

@AccessAnalytic Жыл бұрын

Glad to help

@thuytamdang3390 2 жыл бұрын

Thank a lot for sharing. extremely useful content.

@AccessAnalytic 2 жыл бұрын

You’re welcome Thuy Tam Dang

@AccessAnalytic 2 жыл бұрын

You’re welcome Thuy Tam Dang

@pranjalagrawal4167 Ай бұрын

Worked like a charm. Thank you sir.

@AccessAnalytic Ай бұрын

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

@user-we3nk8dt4g Жыл бұрын

Love the column width hack!

@AccessAnalytic Жыл бұрын

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

@stu4321 Жыл бұрын

Thanks for sharing!

@AccessAnalytic Жыл бұрын

You're welcome

@bibhutisen3197 Жыл бұрын

Great video ❤. Thank you.

@AccessAnalytic Жыл бұрын

You're welcome

@paulskelton8799 3 ай бұрын

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

@AccessAnalytic 3 ай бұрын

You’re welcome

@frankwestphal2883 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

Totally Frank. Glad to help

2 жыл бұрын

Hi Nice video!! I'll try this later. Do you Know if theres any Kind of limitation about how many files can be imported from sharepoint folder?. Again, great video, im learning a lot since i found your channel

@AccessAnalytic 2 жыл бұрын

Thanks Christian. I don't know of a technical limit, I've certainly see 100 done. But the more files you add and the more data there is, and the more complex the transformations there are then the slower it gets. I have staged queries in Excel in the past e.g. 1 consolidation per 6 months of data for example. If using Power BI keep an eye out for my Dataflow demo

@ivanvelasco3118 Жыл бұрын

It was so useful! Thanks!!!

@AccessAnalytic Жыл бұрын

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

@syedabu7685 11 ай бұрын

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

@AccessAnalytic 11 ай бұрын

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

@farizg Жыл бұрын

Awesome! Thank you!

@AccessAnalytic Жыл бұрын

You’re welcome

@jorstube 2 жыл бұрын

Useful Wyn!!!!

@AccessAnalytic 2 жыл бұрын

Cheers Jorge

@twiggyinc1 10 ай бұрын

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

@AccessAnalytic 10 ай бұрын

Awesome!

@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

@nhuluong4005 Жыл бұрын

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

@AccessAnalytic Жыл бұрын

Happy to help

@johnrussell8524 2 жыл бұрын

Thanks for this it will save me so much time! If the resulting file is to be shared by a group of people (within the same organization) do they all have to do the 'sign in' step? It seems unnecessary and inconvenient since they can already access the documents in sharepoint. Is there no way around it?

@AccessAnalytic 2 жыл бұрын

No worries John. Yes they all have to sign in, I know what you mean though.

@pranaysharma8694 Жыл бұрын

Awesome- thanks a lot!

@AccessAnalytic Жыл бұрын

You’re welcome

@shahgul78 Жыл бұрын

Very helpful! Can you explain how to combine Source and Navigation in one step and add to parameter as source

@AccessAnalytic Жыл бұрын

Thanks, you’d need to go into the advanced editor for that and combine the 2 steps into 1 and replace the path with a word like SourcePath, then create a parameter ( via parameter button ) called SourcePath

@Belakavadi Жыл бұрын

Awesome trick. You saved my day.

@AccessAnalytic Жыл бұрын

You're welcome 😄

@mounahamtine6945 11 ай бұрын

Incredible, thank you so much!!!!

@AccessAnalytic 11 ай бұрын

You’re welcome

@mih_a 2 жыл бұрын

Thank you mate!

@AccessAnalytic 2 жыл бұрын

You’re welcome Alex

@MicaelaJoieB.Fajardo 3 ай бұрын

This helps! Thank you!

@AccessAnalytic 3 ай бұрын

You’re welcome

@Misterbigpocket 9 ай бұрын

Thanks sifu..u help me alot..

@AccessAnalytic 9 ай бұрын

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

@Brooklyn-NYLatina Жыл бұрын

This is a great video! Exactly what I was trying to do. Question if I delete data on my PBI will it remove it from the sharepoint file?

@AccessAnalytic Жыл бұрын

Thanks. Power BI is read-only so filtering out any data in Power Query will have no impact on your SharePoint files

@Brooklyn-NYLatina Жыл бұрын

One of the spreadsheets had a table under the other. Now, in PBI I have two different data in one query. Can I remove half of the table into a new query, and would it keep its connection?

@AccessAnalytic Жыл бұрын

Difficult to answer without going into more detail but you can right-click reference a query and build other queries off it.

@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

@aliceliger1007 2 ай бұрын

Thanks a lot for all your videos, incredibly clear and helpfull ! I was wondering, if I need to import only one or two files on a sharepoint, should I use preferably this connection to the sharepoint folder method or the simple web connection to the file directly? which one is the more efficient? thanks:)

@AccessAnalytic 2 ай бұрын

You’re welcome. Use from web when connecting to 1 file.

@davidfamilydoctor9430 Жыл бұрын

"Move to beginning" - Such a time saver

@AccessAnalytic Жыл бұрын

Thanks 😀 All these little things help

@luzkrg 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

You’re welcome Luz

@Girhofamily 9 ай бұрын

Best video I found so far on KZfaq

@AccessAnalytic 9 ай бұрын

Thanks for letting me know 😀

@monicacisneros327 2 ай бұрын

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

@AccessAnalytic 2 ай бұрын

I’m glad you found it useful

@alexfruitservice8380 Жыл бұрын

Thanky you very much!

@AccessAnalytic Жыл бұрын

You’re welcome

@jasons6721 2 жыл бұрын

Nice video. Trying this out. Not sure if it's given me a performance benefit, yet. Curious what you do if you have folders within folders. Do you suggest creating multiple "roots" as the source for subsequent files?

@AccessAnalytic 2 жыл бұрын

Tricky one trying to use SharePoint.Files I think. Could be a future video if I work out the best way forward with that. Reverting to SharePoint.Files and filtering to the root folder will then include the sub folders but that's not ideal

@AccessAnalytic 2 жыл бұрын

Go with Imke's function www.thebiccountant.com/2020/11/16/improve-file-import-from-sharepoint-in-power-bi-and-power-query/

@tigere3528 Жыл бұрын

I drilled through 4 folders, my data definitely loads faster, not sure how

@ryansymons724 Жыл бұрын

Brilliant!!!

@AccessAnalytic Жыл бұрын

Thanks 😀

@user-ly6rx4sc4j 17 күн бұрын

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

@AccessAnalytic 17 күн бұрын

☺️ glad I could help 🤘🏼

@renceyanyow Жыл бұрын

Thank you so much. this is very helpful

@renceyanyow Жыл бұрын

I had issue where in I still have to open all excels file just to have an updated consolidated data.

@AccessAnalytic Жыл бұрын

You’re welcome

@AccessAnalytic Жыл бұрын

Could you explain a bit more about the need to open all the files

@renceyanyow Жыл бұрын

@@AccessAnalytic this vid actually solves the problem. Because last time in order to have a live update on all table. I need to open all of them simultaneously so that i can refresh the data realtime.

@AccessAnalytic Жыл бұрын

Great to know it helped

@AllendePerez 4 ай бұрын

you are now my hero! Thank you

@AccessAnalytic 4 ай бұрын

☺️ Glad to help.

@ev-np2px Жыл бұрын

love the video!

@AccessAnalytic Жыл бұрын

Great!

@ev-np2px Жыл бұрын

@@AccessAnalytic i love this method! extremely helpful. have you encountered any errors in the compiling files step? i am getting an error that says “we didn’t recognize the format of your first file” and says the sharepoint site address. i have successfully used your method, but i am unable to now replicate it. not sure if this is a power bi issue or sharepoint.

@AccessAnalytic Жыл бұрын

It did break for a while but then they fixed it. Are you using an up to date version of power bi desktop?

@ev-np2px Жыл бұрын

@@AccessAnalytic i have the april 2023 version i did notice there is a may update. would the april 2023 have any issues?

@AccessAnalytic Жыл бұрын

Fixed in March version. I’ve just tested in May and still works for me. Maybe remove the first few files from your folder and test again to ensure it’s not a file issue

@bhuvandesai1 Жыл бұрын

Thanks for nice and simple way of explaining it. How can I dynamically pass SharePoint Link and Folder as a parameters so that we do not end-up rebuilding query every year or if same wants to use by multiple department but map with different SharePoint Site and Folder

@AccessAnalytic Жыл бұрын

Technically you can add a Parameters in Power Query (there's a button and wizard to create a parameter) then just replace the hard coded paths with the folder names. The difficulty with the SharePoint.Contents approach is there is an unknown number of folders to drill through so that will be a problem.

@matsiengboyz 5 ай бұрын

Amazing, thank you!

@AccessAnalytic 5 ай бұрын

You're welcome

@Mogiron10 7 ай бұрын

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

@AccessAnalytic 7 ай бұрын

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

@aaronurzua4719 2 жыл бұрын

Thanks for the amazing tip! Is there any way to get the 'Modified by' column?

@AccessAnalytic 2 жыл бұрын

Hi Aaron, doesn't appear so, Date Modified yes, Modified by, no. If you find a way come back and let us know

@EngineerHarris 4 ай бұрын

This is AMAZING! Thank you. This has been driving me absolutely insane! Question: If I have CSV files grouped by different folders - those folders act as categories or groups. I followed your steps and can easily get to what I want for a Single Folder (that contains CSV Files) but what about if also want to repeat the transform for all the other folders. Do I have to keep duplicating the steps you went through here to get all the queries in the same Excel File for all the folders of interest? Or is there a simpler/better way? The data (CSVs) in each folder will get their own Sheet in excel. Thanks!

@AccessAnalytic 4 ай бұрын

If you’re wanting to consolidate files from sub-folders then try out Imke’s solution per this video kzfaq.info/get/bejne/o82Goc5lt52dkZc.htmlsi=uq_bCTMHiYoLD8Lf