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

[@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 😊

[@gabornagy6916]

You are GREAT!!! It took one week for our IT team not to solve my problem, only 2 minutes from this video solved this!!! Thank you!!!!

[@MyOnlineTrainingHub]

Glad it helped!

[@ritenderchauhan3846]

​@@MyOnlineTrainingHub Hello, how can I get the file name as a separate column if I am exporting the data from SharePoint folder?

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

[@l1lagarto]

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

[@MyOnlineTrainingHub]

Thanks so much!

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

[@faiqshad]

i was starting to lose hair over the frustration of being unable to connect to the sharepoint link. i had moved my entire organization from onedrive to sharepoint just to have a common path for power queries. thank you so much for posting this invaluable tutorial.

[@MyOnlineTrainingHub]

I'm so glad this solved your problem! It's a common pain point.

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

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

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

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

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

[@IvanCortinas_ES]

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

[@MyOnlineTrainingHub]

You are most welcome, Ivan!

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

[@RanjitKumar-br2ff]

crisp, clear and to the point. Much appreciated

[@MyOnlineTrainingHub]

Glad it was helpful!

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

[@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 😊

[@Rwt.aje1]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@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 😊

[@0ctopus001]

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

[@MyOnlineTrainingHub]

Glad I could help!

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

[@engineeringinspectionsirel1385]

Struggled with this for soooooo long. Thank you!

[@MyOnlineTrainingHub]

Glad it helped!

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

[@rinlord8326]

omg u literally changed my life from now on

[@MyOnlineTrainingHub]

So pleased this video was helpful 😊

[@nipunmeepage9574]

Thank you, the explanations are very clear and easy to understand.

[@MyOnlineTrainingHub]

Glad to hear that!

[@skip031890]

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

[@MyOnlineTrainingHub]

Glad I could help.

[@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 😊

[@kbriffa]

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

[@MyOnlineTrainingHub]

Fantastic :-)

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

[@sachin.tandon]

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

[@MyOnlineTrainingHub]

Thanks so much, Sachin!

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

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

[@Aqualight33]

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

[@MyOnlineTrainingHub]

Glad I could help 😊

[@patrickmcgill3542]

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

[@MyOnlineTrainingHub]

So pleased to hear that, Patrick!

[@stewartdine]

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

[@MyOnlineTrainingHub]

Glad it helped!

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

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

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

[@germanopillon3674]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@eliezerhilario]

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

[@MyOnlineTrainingHub]

Gracias, Eliezer!

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

[@mirzayasirarfat]

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

[@MyOnlineTrainingHub]

Thanks for your kind words 🙏😊

[@johnzafe]

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

[@MyOnlineTrainingHub]

You're very welcome, John!

[@michaeljones2843]

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

[@MyOnlineTrainingHub]

Happy to help, Michael!

[@z.719]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@nadegeperrier5243]

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

[@MyOnlineTrainingHub]

Great to hear!

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

[@marcosian820]

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

[@MyOnlineTrainingHub]

Thank you!

[@garyallan69]

Thank you. Just what the doctor ordered

[@MyOnlineTrainingHub]

Glad I could help 😊

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

[@jorgevalentin4756]

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

[@MyOnlineTrainingHub]

Glad you enjoyed it, Jorge!

[@cereskinomoto2004]

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

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@vijayarjunwadkar]

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

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@jessiesi1412]

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

[@MyOnlineTrainingHub]

So great to hear!

[@bero5541]

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

[@MyOnlineTrainingHub]

Great to hear 😊

[@ilkercalsr8130]

I learn a lot. Thanks for your sharing.

[@MyOnlineTrainingHub]

My pleasure!

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

[@euniceesinam2573]

Thank you, Linda! You're awesome!

[@MyOnlineTrainingHub]

Glad it was helpful 🙏

[@theprocessarchitect4715]

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

[@MyOnlineTrainingHub]

You're so welcome!

[@unitedeurope8614]

Fantastic! Very well and pleasantly explained

[@MyOnlineTrainingHub]

Thanks so much!

[@kyawzin-kz]

This is awesome. Thank you for posting this video.

[@MyOnlineTrainingHub]

My pleasure 😊

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

[@carmenzheng1597]

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

[@MyOnlineTrainingHub]

Glad I could help 😊

[@pvsraaaa865]

Very useful when we are working with team

[@MyOnlineTrainingHub]

Glad to hear that!

[@giuseppelarosa6130]

Thank you! clear and to the point as always

[@MyOnlineTrainingHub]

Great to hear!

[@sergiohinojosa7092]

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

[@MyOnlineTrainingHub]

Great to hear, Sergio!

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

[@charliecheng1022]

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

[@MyOnlineTrainingHub]

Thanks so much, Charlie 🙏

[@TheZinho159]

Very helpful. Thank you so much!

[@MyOnlineTrainingHub]

You're very welcome!

[@zakincc]

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

[@MyOnlineTrainingHub]

Great to hear, Zakaria!

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

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

[@mohamadraad5038]

very important lesson thank you Myanda - Mohamad form Lebanon

[@MyOnlineTrainingHub]

Glad you liked it!

[@shoaibrehman9988]

Superb content, thanks for your efforts

[@MyOnlineTrainingHub]

So nice of you 🙏

[@JoseLuisMendezCordova-p6j]

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.

[@m1cannas]

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

[@MyOnlineTrainingHub]

My pleasure!

[@saurabhtaneja6179]

Super helpful. Thanks very much Genius

[@MyOnlineTrainingHub]

Glad I could help!

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Thanks for your support, Darryl!

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

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

[@Yunnietje]

Thanks for this solution I was looking for. There is still one problem I want to solve. Is there any solution for refresh the data? If I refresh the data it takes very long instead refresh the data on the C location.

[@moesadr3342]

Awesome video, thank you. What would happen if later on, one or more columns be added, deleted or renamed from the original date file and how we can update the Power query to reflect the changes and/or fixing error messages which will occur during refresh? Thanks

[@MyOnlineTrainingHub]

Thanks, Moe. If columns change that Power Query is relying on then it will break the query and you'll have to edit the steps either in the advanced editor or formula bar so it knows the new names/columns.

[@moesadr3342]

@@MyOnlineTrainingHub Thank you, I will give it a try.

[@genechicago4806]

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

[@MyOnlineTrainingHub]

You are most welcome!

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

[@fanzfanzilla]

Thank you for sharing and the great step-by-step directions to get a list from SharePoint. Is there a way to run scheduled tasks to get data from the SharePoint list? I want to get data from a SharePoint list once or twice a data into a database.

[@MyOnlineTrainingHub]

You can write some VBA to trigger the refresh automatically, but the file must be open for it to execute.

[@Theomatikalli]

Just what I was looking for

[@MyOnlineTrainingHub]

Glad I could help 😊

[@SimonTidd]

Mynda, Thanks for another really useful video. In the example for getting data from a single file, you ended up opening the file in Excel but from OneDrive on the web. I understand this was to illustrate the different paths to the file. Forgive me for not testing this, (as I'm still watching), but do you have to open the workbook from the Web to get the correct path visible in Excel, or would it work the same way if you had opened it from OneDrive on your desktop?

[@MyOnlineTrainingHub]

Thanks, Simon! Yes, you must open the file from OneDrive on the web in order to get the correct path to the file.

[@musicbuddy1382]

You are a life saver 😢

[@MyOnlineTrainingHub]

Glad I could help!

[@DougHExcel]

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

[@MyOnlineTrainingHub]

I hope so too, Doug!

[@victorvivanco7384]

Thank you 😱 you saved me!!

[@MyOnlineTrainingHub]

Glad I could help!

[@jtb5798]

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

[@MyOnlineTrainingHub]

Happy to help 😊

[@dmazimoto]

Excellent video, thank you for the detailed explanation. I'm unfortunately on Excel for 365 and the SharePoint folder connector is not available to me. I am migrating a workbook to get it's data from CSV's located in OneDrive but can't find for the life of me how to extract the "Last Modified Date" from the CSV using power query. Local files was easy enough but OneDrive files I cannot figure out, any thoughts on what I can do? Any help is appreciated.

[@MyOnlineTrainingHub]

The SharePoint connector is only available in Excel 2019/2021 Professional Plus and with a Microsoft 365 Apps for Enterprise license.

[@MKrugz]

Hi! Thank you for your awesome videos. For some reason I do not have a "From sharepoint folder" option. I have a standard business 365 license. Is there a workaround if I do not have that option?

[@MyOnlineTrainingHub]

The SharePoint connector is only available with a Microsoft 365 Apps for Enterprise license. You would have to upgrade your 365 license.

[@hazemali382]

May Thanks very Much Mynda♥

[@MyOnlineTrainingHub]

You're welcome! 🙏

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

[@tomfontanella6585]

Thank you for the great content! Like other viewers, I've referenced this video several times when trying to extract data from a Sharepoint list. While it isn't explicitly covered, the principles are the same. In the process I've discovered that not every field in a list will come across in the extract. Most frustratingly, this happens to a field that contains a name that resolves to an entry in the Active Directory Global Address List. I've created a workaround but would be open to hearing about any solution to this.

[@MyOnlineTrainingHub]

I've not come across that problem before, Tom. Hope you find a workaround.

[@tomfontanella6585]

@@MyOnlineTrainingHub thanks. I would never rule out that it is something I am doing :) but it remains a mystery to me.

[@MyOnlineTrainingHub]

I'd probably give up and move the file to a location that's easier to navigate to 😂

[@vickiemarino2840]

Great video! In order to combine the tables into one, must they all be named the same in each spreadsheet?