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

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

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

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

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

[@l1lagarto]

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

[@MyOnlineTrainingHub]

Thanks so much!

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

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

[@RanjitKumar-br2ff]

crisp, clear and to the point. Much appreciated

[@MyOnlineTrainingHub]

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!

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

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

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

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

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

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

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

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

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

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

[@engineeringinspectionsirel1385]

Struggled with this for soooooo long. Thank you!

[@MyOnlineTrainingHub]

Glad it helped!

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

[@Rwt.aje1]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

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

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

[@sachin.tandon]

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

[@MyOnlineTrainingHub]

Thanks so much, Sachin!

[@euniceesinam2573]

Thank you, Linda! You're awesome!

[@MyOnlineTrainingHub]

Glad it was helpful 🙏

[@nipunmeepage9574]

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

[@MyOnlineTrainingHub]

Glad to hear that!

[@patrickmcgill3542]

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

[@MyOnlineTrainingHub]

So pleased to hear that, Patrick!

[@theprocessarchitect4715]

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

[@MyOnlineTrainingHub]

You're so welcome!

[@skip031890]

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

[@MyOnlineTrainingHub]

Glad I could help.

[@ilkercalsr8130]

I learn a lot. Thanks for your sharing.

[@MyOnlineTrainingHub]

My pleasure!

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

[@rinlord8326]

omg u literally changed my life from now on

[@MyOnlineTrainingHub]

So pleased this video was helpful 😊

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

[@kbriffa]

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

[@MyOnlineTrainingHub]

Fantastic :-)

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

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

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

[@Aqualight33]

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

[@MyOnlineTrainingHub]

Glad I could help 😊

[@stewartdine]

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

[@MyOnlineTrainingHub]

Glad it helped!

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

[@johnzafe]

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

[@MyOnlineTrainingHub]

You're very welcome, John!

[@xaratsarhs]

Hello, great info but what about 365 business users , seems like Microsoft left out the SharePoint connector out , is there any work around? Thanks

[@MyOnlineTrainingHub]

No workaround, sorry. It's frustrating and we MVPs tell Microsoft all the time!

[@michaeljones2843]

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

[@MyOnlineTrainingHub]

Happy to help, Michael!

[@mirzayasirarfat]

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

[@MyOnlineTrainingHub]

Thanks for your kind words 🙏😊

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

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

[@unitedeurope8614]

Fantastic! Very well and pleasantly explained

[@MyOnlineTrainingHub]

Thanks so much!

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

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

[@bero5541]

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

[@MyOnlineTrainingHub]

Great to hear 😊

[@nadegeperrier5243]

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

[@MyOnlineTrainingHub]

Great to hear!

[@mohamadraad5038]

very important lesson thank you Myanda - Mohamad form Lebanon

[@MyOnlineTrainingHub]

Glad you liked it!

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

[@charliecheng1022]

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

[@MyOnlineTrainingHub]

Thanks so much, Charlie 🙏

[@z.719]

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

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

[@schoolfriendsteve663]

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

[@MyOnlineTrainingHub]

My pleasure 😊

[@pvsraaaa865]

Very useful when we are working with team

[@MyOnlineTrainingHub]

Glad to hear that!

[@NaomiDeacon-w3k]

Hi Mynda, thank you so much for all your video's, I've learned so much from you. But I'm still stuck. I've built dashboards in Excel for work which uses about 20 queries to combine data from multiple folders all in a OneDrive folder which when I look at the sharepoint file path, it shows as my boss's personal onedrive which he has given me access to. The datamodel and dashboard I've built works perfectly for me, but even he can't use it because he can't refresh the data because the queries are built with my onedrive file path, and breaks when anyone else opens it. Is it possible to make the onedrive powerquery source file path dynamic so other staff can use the dashboards? I've tried what you've shown in other video's to send people the web link for the dashboard, but they still can't refresh the data.

[@MyOnlineTrainingHub]

It sounds like you may have used the local file path of the sync'd files as opposed to the SharePoint file path as shown in this video. Please post your question and screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@cereskinomoto2004]

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

[@MyOnlineTrainingHub]

Awesome to hear 🙏

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

[@so1zy]

would 'phil' also be able to refresh this query with the links created? noticed that it is tied to your user ID in the file path name, does that impact his ability to refresh the same query?

[@MyOnlineTrainingHub]

I would have to give Phil permission to the file in OneDrive/SharePoint for him to refresh the query.

[@akashk4993]

Just had a doubt what if the data we are getting from sharepoint is very large and complex transformation needs to be done as i am trying to add and create new custom column in power query instead of dax it is taking forever to do it what would be the best practices to do anything would help

[@MyOnlineTrainingHub]

If the column can be calculated in the PivotTable with a measure, i.e. it's not added to the data table in Power Pivot, then do that, otherwise if it's a column you're adding to a table, then do it in Power Query. It'll be worth the wait.

[@vijayarjunwadkar]

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

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@zakincc]

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

[@MyOnlineTrainingHub]

Great to hear, Zakaria!

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

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Thanks for your support, Darryl!

[@jessiesi1412]

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

[@MyOnlineTrainingHub]

So great to hear!

[@giuseppelarosa6130]

Thank you! clear and to the point as always

[@MyOnlineTrainingHub]

Great to hear!

[@m1cannas]

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

[@MyOnlineTrainingHub]

My pleasure!

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

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

[@kyawzin-kz]

This is awesome. Thank you for posting this video.

[@MyOnlineTrainingHub]

My pleasure 😊

[@Skip2MiLuu]

Hi Mynda, this for overview. I have noticed that there seems to be an a videos on KZbin that give different methods to connecting to files and folders. I will back a set up a query that linked to a file stored in my work Onedrive (I suppose that would mean share point). The same query is also connecting to a file on my personal Onedrive. I noticed that the query that connects to the work Onedrive /SharePoint file had no problem refreshing if I had this folder open in the desktop app or browser open, however I ran into issues with the personal Onedrive file. I needed to close the personal Onedrive source file after data change in order to refresh the query without error, but the work Onedrive had no issue. I not sure if I using your method shown in this video, do you experience the same issues using your method?

[@MyOnlineTrainingHub]

I haven't tested refreshing the OneDrive files. I know for files saved on your hard drive or a network drive, they must be closed for the refresh. I wonder if the personal OneDrive file is synced to your hard drive, in which case, it must be closed for refresh.

[@heshamfouad8886]

Hello Mynda Thanks for the walkthrough I am trying to use this with OneDrive Personal not organizational, but I don't get the option to copy path under the File name _or any options_ do you have any idea ?

[@MyOnlineTrainingHub]

It's not available for OneDrive personal.

[@sergiohinojosa7092]

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

[@MyOnlineTrainingHub]

Great to hear, Sergio!

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

[@srmchinni3]

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

[@MyOnlineTrainingHub]

Glad to hear that!

[@AmnaArshad-m6q]

Hello! I do not see Copy Link option when I right click on my file in OneDrive. Can you please help??

[@gborka]

Very useful. Liked the reply under each and every comment, somebody takes the channel promotion in a serious way. Wondering how many likes under comments bring the channel same promotion benefit as one comment.

[@MyOnlineTrainingHub]

Thanks, I do try to make the channel as engaging as possible!

[@shoaibrehman9988]

Superb content, thanks for your efforts

[@MyOnlineTrainingHub]

So nice of you 🙏

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

[@ASTRIX285Gaming]

Hi Mynda, love your videos, one issue that i am facing is the limitation to do Power Query, Power Piviot on a MacBook as there is not Power Query table that i have created into a onedrive link the tabel does not refresh, is there a work around this?

[@MyOnlineTrainingHub]

Thank you! Excel for Mac doesn't have the Web connector, therefore it can't connect to files on OneDrive. You'd need to connect it to the synced version of the file on your hard drive.

[@marcosian820]

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

[@MyOnlineTrainingHub]

Thank you!

[@musicbuddy1382]

You are a life saver 😢

[@MyOnlineTrainingHub]

Glad I could help!

[@TheZinho159]

Very helpful. Thank you so much!

[@MyOnlineTrainingHub]

You're very welcome!

[@chankosol]

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

[@DougHExcel]

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

[@MyOnlineTrainingHub]

I hope so too, Doug!

[@mariaalcala5159]

Hi Mynda! Great video! Thanks!

[@MyOnlineTrainingHub]

Thanks, Maria!

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

[@EricHartwigExcelConsulting]

What would cause a user to not see the "From SharePoint Folder" option under the "From File" pop out menu?

[@MyOnlineTrainingHub]

You don’t have the appropriate user license.

[@EricHartwigExcelConsulting]

@@MyOnlineTrainingHub What are the user licenses that are needed?

[@saurabhtaneja6179]

Super helpful. Thanks very much Genius

[@MyOnlineTrainingHub]

Glad I could help!

[@carmenzheng1597]

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

[@MyOnlineTrainingHub]

Glad I could 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.