Get Data From SharePoint or OneDrive with Power Query - Demystified!

In this video I cover the 3 options for getting data from SharePoint or OneDrive with Power Query:
00:27 From a Single File on SharePoint or OneDrive
04:08 From a SharePoint or OneDrive Folder
08:12 From a SharePoint Shared Library
11:56 Sharing Files with Others
Note: The SharePoint Folder connector is only available in Excel 2019 Professional Plus and with a Microsoft 365 Apps for Enterprise license
Get step by step written instructions here: www.myonlinetr...
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy

[@l1lagarto]

Lots of tutorials, none as simple and easy to follow like yours. Thanks a lot!

[@MyOnlineTrainingHub]

Thanks so much!

[@markusdurm4118]

You are my heroine, that I searched for long time and no one at work had a clue.

[@MyOnlineTrainingHub]

So pleased I could help, Markus :-)

[@BaneIsAlive]

I was trying to figure this out for a week until i came across your video. Youre a life saver.

[@MyOnlineTrainingHub]

Glad I could help 😊

[@rjbush7955]

Very useful. Bottom line Microsoft needs to make this a lot easier getting data from SharePoint. Especially since Power BI uses PQ as its principal ETL. Needing to faff about copying and pasting then editing links is not easy for the average worker who’s not used to data extraction.

[@MyOnlineTrainingHub]

I agree, it could be much easier!

[@JamalHeadley-jv5pe]

Was asking for ages how to get the proper URL and nobody could tell me. This video answered the question in 5 minutes - Thank you!!!!!!!

[@MyOnlineTrainingHub]

So pleased I could help!

[@javiercalderon1961]

This tutorial saved my job!! I don't have enough words to say THANK YOU SO SO MUCH!!! You have blessed my life 😇

[@MyOnlineTrainingHub]

Wow, that's awesome to hear, Javier!

[@aryaa3998]

This is amazing, thank you for sharing! I have to say you make an excellent teacher. Very natural, coherent, interesting, correct pacing, nice voice, confident, intelligent and so on. I sound incredibly boring when presenting XD

[@MyOnlineTrainingHub]

Wow, thank you so much!

[@joshuaerkman1444]

I wish I had seen this earlier! I spent nearly 100 hours, developing a naive Bayes classifier to solve a particularly tricky identity problem, to determine the proper primary key in a database. This would have been so helpful for my methodology. Awesome to see someone else using Excel for this!

[@MyOnlineTrainingHub]

So pleased it was helpful, Joshua!

[@loolasim]

This was baffling me for several days. on google there were so many confusing codes, but your video showed a much clearer and (comparatively) simpler way. this was really helpful, thank you so much!!

[@MyOnlineTrainingHub]

Glad it helped, Lauren!

[@Ernestofranco80]

The way you walk through this is amazing. So simple and I just need to find you when I get stuck. Every answer I find through you. Thank you so much

[@MyOnlineTrainingHub]

Glad I could help!

[@martinargimon730]

Hi mYnda, I have revisited this video of yours one again, as more and more , we are all using a cloud service ( onedrive in my case). You are definitively one of the best instructors and your knowledge is excellent. I wish you the best in your business and many thanks for helping us , in this ever increasing complicated world of IT. Kind Regards. Martin

[@MyOnlineTrainingHub]

Thanks for your kind words, Martin!

[@RanjitKumar-br2ff]

crisp, clear and to the point. Much appreciated

[@MyOnlineTrainingHub]

Glad it was helpful!

[@rafaelboschetti3741]

Awesome! I've been using power query for years now and I just did not understand how to make the connection work by reading some tutorials online, you made it really simple. Thank you so much!

[@MyOnlineTrainingHub]

So glad it was helpful! 😊

[@IvanCortinas_ES]

Power Query and SharePoint. Relax and action! What a powerful tool!! Thank you Mynda.

[@MyOnlineTrainingHub]

You are most welcome, Ivan!

[@1601mat]

You provide a tremendous service. This video enabled access to the centralized data files by my MS Teams members---and saved the day. Thank you.

[@MyOnlineTrainingHub]

Wow, that's awesome to hear, Mat!

[@alchemistdreams]

I didn’t even know this was an issue as I’ve never needed someone else to do a refresh. Thank you! I’ll know for the future.

[@MyOnlineTrainingHub]

You're welcome!

[@dipaktamang3095]

Thank you ... I needed to watch only the first 2 minutes to find what I was trying to do in the last 2 hours...

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@DiamondRock2]

Thank you Mynda, Power Query is a very important tool and you make this tool easy to use step by step. We appreciate your effort.

[@MyOnlineTrainingHub]

Thanks for watching!

[@chandramohan7155]

This video is a saver to me, I was unable to get an idea to what extent I need to copy the SharePoint URL. Thanks a ton.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@0ctopus001]

I was just a µ before giving up. Thank you for the solution and the good channel.

[@MyOnlineTrainingHub]

Glad I could help!

[@engineeringinspectionsirel1385]

Struggled with this for soooooo long. Thank you!

[@MyOnlineTrainingHub]

Glad it helped!

[@miguelmr8869]

Thank you Mynda, I always wanted to perform PQ with a Teams shared file and I didn't know how. With your explanation in this video, I have solved it. This advice is very useful for me. Thank you

[@MyOnlineTrainingHub]

Awesome to hear, Miguel!

[@quentinbricard]

Great! Thank you for this video! I now have to "re-map" 16 local files paths relations to OneDrive paths. 😵‍💫😵‍💫😵‍💫 My life was much easier before watching this video, I was assuming "nah, it's not possible" 😂😂😂

[@MyOnlineTrainingHub]

😆 it’ll be worth it!

[@Mahmoud-mf8hn]

I cant thank you enough for what you are doing! You are really helping us raising the bar!

[@MyOnlineTrainingHub]

So pleased to hear that 😊

[@ajayrawat8311]

The video I was looking for the last few days. Thank you a ton, Tracy. Keep Rocking.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@readmycomment4696]

This was The Best, I tell you, the best video on the topic, I saw few other before landing on this one, they were unnecessarily long and made complicated. I saw this, learnt what I wanted, implemented it successfully. Thank you my life saver :)

[@MyOnlineTrainingHub]

Wow, thanks!

[@dannymccann]

Mynda this video was perfect. Why Microsoft have decided to make OneDrive/SharePoint cloud integration into a key selling point feature such as Power Query so difficult and fiddly we can only imagine - hopefully the process becomes streamlined because even now after your excellent tutorial it is a daft amount of work when the On Premise solution is generally faultless.

[@MyOnlineTrainingHub]

Yes, I agree it could be easier.

[@patrickmcgill3542]

Excellent! This came a the most appropriate time because I have been struggling with this.

[@MyOnlineTrainingHub]

So pleased to hear that, Patrick!

[@kbriffa]

I've been trying to find how to do this for months! Endless thanks!

[@MyOnlineTrainingHub]

Fantastic :-)

[@pipelon020]

This video is gold in times of desperation. I was looking for this content, thank you very much! I will subscribe

[@MyOnlineTrainingHub]

Glad I could help, Andres! Thanks for subscribing 😊

[@mirzayasirarfat]

This is wonderful, thank you for sharing! you are a excellent coach.

[@MyOnlineTrainingHub]

Thanks for your kind words 🙏😊

[@skip031890]

This is exactly what I was looking to learn how to do. Thank you.

[@MyOnlineTrainingHub]

Glad I could help.

[@germanopillon3674]

Finally I found the video I wanted! Thanks for sharing this usefull content

[@MyOnlineTrainingHub]

Glad it was helpful!

[@ryszardsowa6263]

Really great video, first one that solved my problem of drilling to proper SharePoint folder in my Power Querry. Thanks!

[@MyOnlineTrainingHub]

Great to hear!

[@rinlord8326]

omg u literally changed my life from now on

[@MyOnlineTrainingHub]

So pleased this video was helpful 😊

[@craigjohnwatson]

Thank you, a very clear and easy to follow video which solved my challenge (changing the data source to the company's Sharepoint directory). I was even able to adapt Option 1 to work with CSV files too.

[@MyOnlineTrainingHub]

Glad it helped, Craig!

[@michaeljones2843]

Awesome tip!!! Thank you, I've been trying to do this for months.

[@MyOnlineTrainingHub]

Happy to help, Michael!

[@Aqualight33]

Thank you so much for this video. Took me ages to find a solution!

[@MyOnlineTrainingHub]

Glad I could help 😊

[@marcosian820]

You´re amazing with the explanation, in other hand microsoft are so burocratic that hurts

[@MyOnlineTrainingHub]

Thank you!

[@sachin.tandon]

Well prepared video Lynda. Keep working with such enjoyment and enthusiasm!

[@MyOnlineTrainingHub]

Thanks so much, Sachin!

[@johnzafe]

Thank you so much! I have no words to describe how helpful this is

[@MyOnlineTrainingHub]

You're very welcome, John!

[@stewartdine]

Thank you so much for this video. It has solved a massive headache for me 😊

[@MyOnlineTrainingHub]

Glad it helped!

[@charliecheng1022]

思路清晰，内容实用。老师很棒🔓

[@MyOnlineTrainingHub]

Thanks so much, Charlie 🙏

[@euniceesinam2573]

Thank you, Linda! You're awesome!

[@MyOnlineTrainingHub]

Glad it was helpful 🙏

[@martinsefelin4479]

Really amazing! I can’t wait to try this! Been battling with SharePoint/ MS excel communication friendship for a while now. Seems like fun weekend is coming up😂. Love your content and thanx for helping me to be a better excel nerd 😀 Much Love! Martin

[@MyOnlineTrainingHub]

Glad it was helpful, Martin!

[@garyallan69]

Thank you. Just what the doctor ordered

[@MyOnlineTrainingHub]

Glad I could help 😊

[@jorgevalentin4756]

What an amazing video, simple and intuitive. Thank you so much!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Jorge!

[@jessiesi1412]

Thank you so much! Your video helps with my work. ❤

[@MyOnlineTrainingHub]

So great to hear!

[@huutai7960]

I got it, thank you, at 2:45 i choose Organizational account tab for logging in cuz share point file is in limited access.

[@sabarashid1513]

Best as always....thank you for the content you create and share. It really helps me on daily basis

[@MyOnlineTrainingHub]

Happy to hear that!

[@stevet3331]

Massive thank you for this video, Mynda. I visualise data using PBI, but the source file and pbix are on my computer. They need to be accessible from our shared SP site. I have been unable to find the way to get the SP link issue to work, with numerous different error messages. I was maybe, 80% there, but not there! This vid has been invaluable in showing the way. Who knew! Thanks.

[@MyOnlineTrainingHub]

So pleased it was helpful, Steve!

[@karenholden5740]

Thank you. I have learned so much on this video.😊

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@theprocessarchitect4715]

Well this helped a lot! Thank YOU for creating this video!!! :)

[@MyOnlineTrainingHub]

You're so welcome!

[@ilkercalsr8130]

I learn a lot. Thanks for your sharing.

[@MyOnlineTrainingHub]

My pleasure!

[@yiubili1]

Thanks great content as always! Also love the tips about sharing it at "11:56​ Sharing Files with Others"

[@MyOnlineTrainingHub]

Glad it was helpful!

[@abirahmed1892]

It’s really very helpful for me, thank you so much for this, i got exactly what I’m looking for. 👍

[@MyOnlineTrainingHub]

Awesome to hear!

[@DougHExcel]

Thanks for the 3 ways to do this...hopefully MSFT makes it easier in the future 🤔

[@MyOnlineTrainingHub]

I hope so too, Doug!

[@vijayarjunwadkar]

Thanks a ton Mynda! I was just looking for this and got the best to solve my problem! 😊👍

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@pvsraaaa865]

Very useful when we are working with team

[@MyOnlineTrainingHub]

Glad to hear that!

[@zakincc]

You’ve been a great help in making me learn and improving my productivity. Thank you so much ❤️

[@MyOnlineTrainingHub]

Great to hear, Zakaria!

[@nadegeperrier5243]

Amazing video, you've saved me few hours ! Thank you for sharing !

[@MyOnlineTrainingHub]

Great to hear!

[@eliezerhilario]

Excelente video. Me ha sido muy útil. Muchas gracias Tienes un seguidor más desde República Dominicana 😃

[@MyOnlineTrainingHub]

Gracias, Eliezer!

[@schoolfriendsteve663]

Thank you, again, for being an excellent resource for solving these data query challenges :)

[@MyOnlineTrainingHub]

My pleasure 😊

[@srmchinni3]

This is very useful to me. Thank you so much.

[@MyOnlineTrainingHub]

Glad to hear that!

[@bero5541]

You're amazing. We appreciate the high quality content.

[@MyOnlineTrainingHub]

Great to hear 😊

[@z.719]

Very useful . Have been searching for this for quite a while. 👍

[@MyOnlineTrainingHub]

Glad it was helpful!

[@mohamadraad5038]

very important lesson thank you Myanda - Mohamad form Lebanon

[@MyOnlineTrainingHub]

Glad you liked it!

[@Theomatikalli]

Just what I was looking for

[@MyOnlineTrainingHub]

Glad I could help 😊

[@unitedeurope8614]

Fantastic! Very well and pleasantly explained

[@MyOnlineTrainingHub]

Thanks so much!

[@saurabhtaneja6179]

Super helpful. Thanks very much Genius

[@MyOnlineTrainingHub]

Glad I could help!

[@donmcgimpsey1706]

Works great and did solve my problem. The only problem I have now is that it takes around 1 hour to scan and filter the proper files, there are only 300k records coming back but it is all the overhead on my OneDrive. I am thinking that I can ask IT to allocate a dedicated OneDrivethat I can use for this application and potentially other similar use cases, where it is not competing with 2TB of other files.

[@donmcgimpsey1706]

Update: I was able to put my data in a teams site, and since there are only a few files (lots of folders) it was very fast, under 20 seconds to run the Dax. Thanks so much!

[@MyOnlineTrainingHub]

Wow, awesome to hear, Don!

[@m1cannas]

I'm strulling with the content. Thanks a lot.

[@MyOnlineTrainingHub]

My pleasure!

[@sergiohinojosa7092]

Wooow! Just what I've been looking for! Thanks!

[@MyOnlineTrainingHub]

Great to hear, Sergio!

[@giuseppelarosa6130]

Thank you! clear and to the point as always

[@MyOnlineTrainingHub]

Great to hear!

[@dafeac]

what a life saver, can't believe is 2023 and there is no easy way to connect to sharepoint on office business standard, how do you come across this workarounds

[@MyOnlineTrainingHub]

Glad I could help. Workarounds come from all sorts of places. In this case it was from another Microsoft MVP, but sometimes it's Microsoft themselves, forums, other users and trial and error.

[@carmenzheng1597]

Thank you so much for this! I could not figure it out.

[@MyOnlineTrainingHub]

Glad I could help 😊

[@shoaibrehman9988]

Superb content, thanks for your efforts

[@MyOnlineTrainingHub]

So nice of you 🙏

[@user-km2xd2sq4v]

Thanks for the help, it's a very good video. Could you please explain more how other users can update the same consolidated file, since I have tried and still have not been able to get other users from the same corporation to update it? this is so that different users can update the same (consolidated) file containing the relationships and connections

[@MyOnlineTrainingHub]

Sounds like a permission issue. Not something I'm very experienced in, but if you have SharePoint then I'd reach out to your IT admin and ask them to check if they have a setting restricting file access.

[@taharalloui5897]

Fantastic! thank you!

[@MyOnlineTrainingHub]

Glad you liked it!

[@biljanamajstorova3332]

Thanks for sharing solution that works 🙏 why MS made it so complicated?

[@MyOnlineTrainingHub]

Who knows! Hopefully it will be easier in future.

[@genechicago4806]

Awesome! This is very helpful! Thank you for sharing!

[@MyOnlineTrainingHub]

You are most welcome!

[@kyawzinlatt3712]

This is awesome. Thank you for posting this video.

[@MyOnlineTrainingHub]

My pleasure 😊

[@SuperBermondo]

You are always my best for every MSFT new tool. I made it with a file but not a folder! Apparently, one must have Excel Enterprise to be able to connect to the SharePoint Folder (with my Business package, the option is not listed). I have also tried this with PBI but in vain. It would be interesting to see you taking us through the same exercise with PBI. I have watched some related KZbins but not helpful so far- Thanks again

[@MyOnlineTrainingHub]

Thanks, Nick! The process in Power BI is the same since it uses the same Power Query tool that we have in Excel. Let me know if you get stuck.

[@SuperBermondo]

@@MyOnlineTrainingHub ... And is it a requirement to have Excel E to be able to connect to SharePoint Folder? For SharePoint Folder to appear on the list

[@MyOnlineTrainingHub]

No, Power BI is not dependent on a Microsoft 365 license.

[@SuperBermondo]

@@MyOnlineTrainingHub I am still referring to Excel itself. Is it a requirement to have Enterprise to be able to connect to a SharePoint folder? As for me, I have the business version and I am unable to. I can do for a SharePoint file but not folder as the option does not appear in the first place

[@MyOnlineTrainingHub]

SharePoint folder connector is only available with Office 2019 Professional Plus Standalone and Microsoft 365 Apps for Enterprise, as you can see here: support.microsoft.com/en-us/office/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16

[@gauravsrivastava4602]

You are awesome! THank you!

[@MyOnlineTrainingHub]

Glad it helped!

[@victorvivanco7384]

Thank you 😱 you saved me!!

[@MyOnlineTrainingHub]

Glad I could help!

[@jessiesi1412]

Very helpful. Thank you’

[@MyOnlineTrainingHub]

Great to hear!

[@darrylmorgan]

Hi Mynda!Great Tutorial,Really Helpful Tips...Thank You :)

[@MyOnlineTrainingHub]

Thanks for your support, Darryl!

[@jtb5798]

You've solved my problem! Thanks a lot!!!

[@MyOnlineTrainingHub]

Happy to help 😊

[@Skip2MiLuu]

Thanks for the reply both personal Onedrive and work Onedrive files are synced to hard drive with AutoSave On. Just found it interesting that I can have work Onedrive files open in excel on either the desktop or browser open and still sync the related query but not on personal Onedrive. Was just interested to see what your and other users experience was to see if this was just something I was experiencing and others not.

[@MyOnlineTrainingHub]

I can't have ODB or ODP files open and refresh queries.

[@bacerlona79]

Useful video solved my problem! How about I am using excel getdata from a folder in sharepoint? My dataset are combined from 100+ files that save in one sharepoint folder.

[@MyOnlineTrainingHub]

Glad it was helpful! In theory, you can get data from your 100+ files, but I have no idea about the performance impact that many files would have. You'd have to test it.

[@vigneshwaran8964]

Thank you so much...but I am stuck at 4:36....mine shows only share point list not share point folder ...plz help me on this

[@MyOnlineTrainingHub]

Sounds like you have a version of Excel that doesn't have the right connector.

[@nicolosacco932]

Hello! I'm trying to follow the video but I noticed that the "select from sharepoint folder" button is missing from my excel. Do you know why? What can I do? I am using Microsoft 365 with business premium license.

[@rudiklein]

It does work differently when using a non-business environment like a home/family account. There are similar methods for such environments, but they are more of a hassle and it appears that the derived links are temporary.

[@MyOnlineTrainingHub]

Yes, I mention that this doesn't work for personal OneDrive accounts, which is a shame.

[@gulatinaveen]

Any method by which we can use onedrive personal?

[@maheshsathya100]

This was really really helpful. Thanks a ton

[@MyOnlineTrainingHub]

Great to hear 😊

[@pravinshingadia7337]

Thanks - this was brillaint

[@MyOnlineTrainingHub]

Glad I could help!

[@wordprint]

Great Video. Thank you. I have done the linkages (Combine 7 files into a single sheet via Getdata) all ok except that my colleagues whom I have granted access to my OneDrive Sharepoint Folder still cannot do a "Refresh" on their account. Where could the error be coming from?

[@ahv5457]

Thanks for the video. I'd like to load each of those Binary rows into a separate table rather than combining them into one single table because they all have different contents (columns and rows). I have saved all these tables in one path in a SharePoint. How to da that?

[@MyOnlineTrainingHub]

Each query generates a separate table. If you want your data in separate tables, then you have to create multiple queries, one for each table you want.

[@ahv5457]

@@MyOnlineTrainingHub How to do that? When I click on the first binary row it creates a source and navigation and then if I want to create a second query for the second binary, I have to go back to the source but when I go back to the source and select the second binary row the first one disappears because the navigation step refers to the second binary row. I tried to add two lines of content an paths to load both binary lines but didn’t work.

[@ahv5457]

@@MyOnlineTrainingHub So, possibly I should create a blank query for each binary source and copy the M code from each source to the blank query one by one and then delete the main query created from the SharePoint.

[@MyOnlineTrainingHub]

Yep, it's tedious!

[@mariaalcala5159]

Hi Mynda! Great video! Thanks!

[@MyOnlineTrainingHub]

Thanks, Maria!

[@chankosol]

Thanks, It's helpful, can you share how to generate reports from multi lists in sharepoint with excel?

[@denizkeretli2111]

Amazing! thanks a lot

[@MyOnlineTrainingHub]

Glad you liked it, Deniz!