Change Power Query Folder Connector when Moving to a SharePoint Folder

[@Earth-360]

Absolutely legend! Lot of top you tubers got wrong here. You saving us. Bless you my friend. 🙏

[@AccessAnalytic]

You’re welcome.

[@april_bug]

Absolute legend! Worked perfectly! Brilliant videos, very well spoken, easy and clear to follow and a good length! Not too long, really appreciate your videos. You're my go to for power query and power bi Thank you very much for this!

[@AccessAnalytic]

Fantastic to hear. Thank you 😀

[@AmanGupta-in2zb]

Thanks Sir, It is really usefull and I was searching it for 5 days

[@AccessAnalytic]

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

[@paladinhobbies5206]

Woohooo!!! It worked!!! Been stressing out how to make it work when I came upon this video. Thanks brother and God bless you! Liked and subscribed. :)

[@AccessAnalytic]

Glad it helped, welcome to the channel 😀

[@esetiawan]

This was very helpful thank you so much! Appreciate the time you take to explain this step by step!

[@AccessAnalytic]

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

[@bjorns1039]

Awesome! How a 6 minute video can save hours! Thanks!!

[@AccessAnalytic]

Glad it helped

[@sergejamie440]

Great video, can i ask if this method clears the mapped folder connection from the data source options?

[@AccessAnalytic]

I would think so

[@pgaeh]

Great job on this Wyn....Thanks for sharing.

[@AccessAnalytic]

Thank you for letting me know you liked it

[@EdSongoku]

Thank you! Our network was moved to Sharepoint last week and I've been having nightmares about this, all my files of course stopped working and the thought of having to redo everything had me wanting to jump out the window. I'm giving this a go tomorrow, thank you!

[@AccessAnalytic]

Let me know how it goes Eddie

[@EdSongoku]

@@AccessAnalytic so i tried it out today and it did work! After that i actually tried creating another file with a brand new query also connecting to a Sharepoint folder and it also worked BUT for some reason it takes a very long time to update now 😳 updating usually would take a couple of seconds but now I'm left looking at the screen for about 4 minutes. What could be happening?!

[@AccessAnalytic]

@@EdSongoku It is slower , as there's a lot of metadata to filter through to get to the right folder. You can likely speed things up by changing the source refrerence to SharePoint.Contents and navigate down through Documents and various sub folders to get to the same spot ( I'd recommend doing this in a brand new query and then referencing it with your existing queries ) See around minute 3.30 on my brief suggestion. Since then I've had more and more experience of SharePoint.Contents being the way to go

[@EdSongoku]

@@AccessAnalytic good morning! I just tried doing the new query with the SharePoint contents like you suggested but this is all very new to me i haven't been successful. I see the main folders but then I don't know how to make it drill down to the one I want.

[@AccessAnalytic]

@@EdSongoku Hi, click on the word Table to the left of the word Documents, then you see the next folder, so click Table next to the folder name and so on

[@putrinabilaauliya]

Thanks a lot! It works for me.

[@AccessAnalytic]

Great!

[@krislagocki2286]

Thank you so much. Saved me a lot of time!!

[@AccessAnalytic]

Glad to help Kris

[@davidfamilydoctor9430]

Unexpected solution. Felt like a plot twist.

[@AccessAnalytic]

Not sure if that’s good or bad? 😀

[@davidfamilydoctor9430]

​@@AccessAnalytic Definitely good. A shrewd solution that I didn't expect. Queries to reference the source, then the source can be changed for all queries easily.

[@AccessAnalytic]

Glad it was helpful 😀

[@lenzypetty9371]

Really helpful!!! Thanks so much!

[@AccessAnalytic]

No worries

[@diederikvankolfschoten47]

Yes, been waiting for this, thnx 👍😉

[@AccessAnalytic]

No worries

[@wayneedmondson1065]

Hi Wyn. Interesting procedure. In my work, I don't have to use SharePoint, but nice to know the options, if the need arises. Thanks for sharing :)) Thumbs up!!

[@AccessAnalytic]

Thanks Wayne

[@suheilsamara7955]

Indeed, very helpful tips from you Wyn as usual. I have used your SharePoint.Contents method to change the consolidation from local drive to ShP for 15+ queries and they all work perfectly. Also, having the new file path in a separate referenced query is a great method since we are going to change the SharePoint site again in the near future. One thing I am struggling with though, moving the consolidation of multiple excel files that are located in subfolder under a parent folder and how to refernce the Sample query (First file). Any suggestions! Appreciated.

[@AccessAnalytic]

Hi Suheil, not something I’ve tried. First file refers to folder in source step, so I’d imagine you’d need to edit that

[@mowgli536]

Hello - I actually need to do the exact opposite. Move a connection from SharePoint to a Network Folder. Can I assume I just follow and do the opposite?

[@AccessAnalytic]

Yep absolutely

[@mvlastean]

Hi! I really appreciate your tutorials and find them easy to follow and useful. Do you have any advice about a workaround to use PowerQuery (Excel) to load files from Sharepoint Folder using Office 365 for Business where this connector is not available but strangely for Office 365 Enterprice or Office 2019 only? If I use OneDrive for loading files from folder then this takes the path where OneDrive is sync on my computer and not working of course for someone else which try to refresh the query …

[@AccessAnalytic]

Hi Mircea, glad you like the videos. Try putting this code in the source step of Power Query : = SharePoint.Contents("YOURCOMPANY.sharepoint.com/sites/YOURSITENAME", [ApiVersion = 15]) and replace the BOLD items with the appropriate words. You should then see a list of folders, click on the word Table to the left of either the folder name you want or sometimes it's shared documents.

[@AccessAnalytic]

If you are trying to edit an existing query it will overwrite existing steps unfortunately. So do a seperate new query and use the advanced editor to copy the code into the old query ( replacing the old steps )

[@JacksonDad1]

What if I am not consolidating multiple files and I want to point to just one file within a SharePoint folder? Is there a syntax in the Source location that references the new SharePoint query and the particular file I want? Thanks!

[@AccessAnalytic]

Does this video help? kzbin.info/www/bejne/f5_RhJmvpLSna7s

[@JacksonDad1]

@@AccessAnalytic Yes! Thank you very much. You saved me hours upon hours and slowed the spread of my grey hair.

[@AccessAnalytic]

@@JacksonDad1 Awesome.. I know how it goes....!

[@MilhousekCZ]

Hi Wyn, thanks for sharing your knowledge, really helpful :) I have one challenge - have you ever tried to create SP folder path dynamically based on a file location? Imagine I have a controlling file, harvesting data from a SP subfolder.. so far so good.. But I have 45 folders/files like that and would like to create a SP path, so it automatically changes based on actual file location.. so when copied to next folder, automatically gets the current folder name+data ... any chance you've seen this somewhere? :D Thanks, man

[@AccessAnalytic]

You can create a cell reference in Excel that is dynamic and reference that in Power Query Something like =TEXTBEFORE( CELL("filename",A1),"/[")

[@2005lesea]

Hi, I’m trying to follow this but struggling to compare what exactly to paste as you’ve got it blurred there. Are you pasting the -my.sharepoint/personal/name.com/? I need other people to be able to access and edit this query not just me. What link to I change to please? To mention, we’re moving from local drive to OneDrive and now messing about with source changing to queries I previously created. Individual sheets work fine no issues but I’m struggling with source for combined query from folder with multiple sheets. Thanks

[@AccessAnalytic]

If OneDrive then the path will be XYZ-my.sharepoint.com/personal/YOUR_E_MAIL/ I'd recommend SharePoint instead of OneDrive if posslble (as OneDrive is your Personal verison of SharePoint and gets deleted when you leave the organisation) If SharePoint looks like this XYZ.sharepoint.com/sites/SharePointDemo/Shared%20Documents/Forms/AllItems.aspx you need this bit XYZ.sharepoint.com/sites/SharePointDemo