How to use Power Query to connect to a file on OneDrive or SharePoint (⚠️ see update in links below)

[@martinsefelin4479]

Oh man! You won a badge “My hero of the day”. This bugged me for some time now. I really cannot thank you enough for making this vid! Thanks 🙏🏻 a lot. Martin

[@AccessAnalytic]

Cheers, you’re welcome Martín 👍🏼

[@HarryBourneOnline]

2 days of struggling with Google searches to figure this out as a relatively new Power Query user, solved in 20 minutes. Thank you Wyn!

[@AccessAnalytic]

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

[@jasontan4730]

THANKS Wyn! U my life saver here! I been struggling to to figure out the correct path to use for OneDrive Personal, Business and SharePoint for almost a month now.. Can't find great any KZbin video that talks about it. But really thankful that you shared your video here. Million thanks!!! :)

[@AccessAnalytic]

You're welcome Jason. Not sure it's possible with OneDrive personal. Glad it helped though

[@Scott93312]

So I realize that this video isn't exactly new, but I just discovered it and it made a world of difference with the files I'm developing at my work. Thank you so much for sharing your knowledge!

[@AccessAnalytic]

Cheers Scott, make sure you check out my updated version 😀

[@nathanhaak631]

love your text commentary on random lol moments throughout the video

[@AccessAnalytic]

Thanks Nathan

[@bhakthasingh8198]

Thank you so much. I got stuck almost for 15 hours resolving the connection issue. None of our IT Support could help me but this video.

[@AccessAnalytic]

You’re welcome. Thanks fir letting me know it helped you

[@lingjingliu5762]

Great tips on getting sharepoint folder path! I had the problem but luckily found your video. Thank you!

[@AccessAnalytic]

Glad it helped you Lingjing

[@summalai]

I've been stuck here for a few weeks. Thank you so much!!!

[@AccessAnalytic]

Glad it helped you Summa. Thanks for letting me know

[@AshleighDeLange]

The tip at @14:05m helped me to solve a problem I have been working on for almost 2 weeks. Now to test the outcome.

[@AccessAnalytic]

Great!

[@AccessAnalytic]

Make sure you check out my newer videos on this too

[@FutureCommentary1]

Cackling here... You just saved my life. I prefer working from home but then my updates are not available at work and I COULD NOT figure out how to insert the URL path! Thank you so much.

[@AccessAnalytic]

Glad to help, it’s a very hidden process. Make sure you check out the updated easier version of my video I did recently

[@iankr]

Many thanks, this is very useful. I’m starting to use Power Query at work and needed to learn how to get data from multiple Excel files in a SharePoint shared Library - the number of files increases by one each month. Feeling more confident now, having seen your video. Good grief, Microsoft need to make this easier! I had already voted on their User Forum. Many thanks once again.

[@AccessAnalytic]

Thanks for letting me know you found this useful Ian. The Power Query team are aware this is a poor experience. Fingers crossed they are working on something better.

[@alcantey]

Thank you very much!! Been churning for days working on a solution for this. Now I can see into co-workers shared workbooks in their personal onedrive folders. 👍

[@AccessAnalytic]

No worries Al

[@Зле_Коте]

Thank you! Really help me and my colleagues

[@AccessAnalytic]

Great, make sure you watch the updated version of this video too ( link in the description )

[@sylvainzigoni4237]

Thanks for the video, it helps a lot ! I gonna go with the first method

[@AccessAnalytic]

Thanks Sylvain, I’m about to release a new video on this topic later today. So check back tomorrow to watch an even quicker way to connect to an Excel file on OneDrive / SharePoint

[@nadegeperrier5243]

Thank you so much for sharing !

[@AccessAnalytic]

You're welcome Nadege Make sure you watch my newer version kzbin.info/www/bejne/rIG5Z2qIgayihck

[@MRExcel-bm2qv]

Thanks Wyn , it's very useful steps for me

[@AccessAnalytic]

Glad to help

[@nash0175]

Thanks very much, really helping ! Still wondering how to setup permissions on the source, if this query is refreshed by another user ?

[@AccessAnalytic]

Need to share the source file or folder with the end user

[@DougHExcel]

omg...that is so painful. Thanks for showing it though, I'm glad our IT org lets us map to network drive and gives a facility to have an interface to get text files with just plain http

[@josephansah]

Thanks a lot for putting this video together. Humorously informative, very useful and relevant. It's surprising how unwieldy Microsoft have made something which should be one of the easiest parts of the process 🤦‍♂️ . Voting for change 😃

[@AccessAnalytic]

Thanks for voting !

[@diegolozano2397]

Thaks a lot your video has been soooo usefull. a big hug from colombia

[@AccessAnalytic]

Thanks Diego, make sure you take a look at the updated version of this video. Thanks for leaving a comment.

[@diegolozano2397]

@@AccessAnalytic can you sendme the url of the new video please. 🙏

[@AccessAnalytic]

@@diegolozano2397 here you go kzbin.info/www/bejne/rIG5Z2qIgayihck and there's a related playlist here kzbin.info/aero/PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un

[@juliepomposo8607]

This is brilliant! Thank you for this.

[@AccessAnalytic]

Thanks Julie, make sure you check out my even easier way to find the URL link kzbin.info/www/bejne/rIG5Z2qIgayihck

[@nigelbmichaels]

Thank You for your persistence Wyn, just what i was looking for.

[@AccessAnalytic]

No worries Nigel

[@sarahdeschene3152]

Wow, I just wrestled with this issue in MS Teams this morning. I’m looking forward to trying out some of the tips. A nice simple button would be great. Can’t believe a year after this was posted it’s still THIS hard.

[@AccessAnalytic]

Make sure you check out my updated video here: kzbin.info/www/bejne/rIG5Z2qIgayihck

[@leebecker8255]

Hi, where are you based? You sound English (or maybe Welsh...), but when I clicked through to your website it looks like you're based in Australia? I'm looking for an in person training course in the UK...

[@AccessAnalytic]

I am based in Perth, Australia. Originally from Wales. Maybe check out Alan Murray www.linkedin.com/in/alanmurray-computergaga

[@leebecker8255]

@@AccessAnalytic Ah amazing, thank you so much!! (Sorry for nearly accusing you of being English...!!)

[@Fox252-e2p]

Big help thank you! My file is unique daily (filename_date) and I'm struggling how to make the content step dynamic. I.e. when clicking Binary, PQ hard codes the file name

[@AccessAnalytic]

Normally best if you can set up file called "Current" and just save over the top of that each day. However you could maybe use the From Folder method and then filter to the most recent file before using the expand option. kzbin.info/www/bejne/pIHPo4SLl6qhatE

[@pascalpoulin2007]

Hi, thank you this is very useful to connect to a file. Do you have a video on linking to a full folder so PowerQuery gets data from all the files from a folder so if you add, for instance, the month April.xlsx to the other files already there (i.e. Jan.xlsx, Feb.xlsx and Mar.xlsx) then it will simply update the PowerQuery with the newly added data? I can make that with standard links to my desktop but I am still unable to do this from a Sharepoint folder... Thank you in advance!

[@AccessAnalytic]

Hi Pascal, thanks for the kind feedback. Re From Folder : At the 8:45 mark I click on the word Binary as I only have one file in that folder that I’m interested in. What you should do instead (where you have multiple files in a folder ) is click on the expand icon just above the word binary. Hope that helps / makes sense. Let me know

[@nordisch1966]

Great explanation! Thank you very much for this!

[@AccessAnalytic]

Glad it was helpful, thanks for letting me know Frank

[@uweseidel8784]

This video is GOLD!!! Thanks a lot for sharing

[@AccessAnalytic]

Thanks Uwe

[@hasankocyigit]

Hi, many thanks again, I have one more question; if we work with more than one files in a folder instead of single file, how can we do that? Thank you very much

[@AccessAnalytic]

Check out this video Hasan: Combining Multiple Files from a folder using Power Query in Excel or Power BI. kzbin.info/www/bejne/pIHPo4SLl6qhatE

[@hasankocyigit]

🙏

[@hasankocyigit]

Thank you for sharing, nice advices... Just I have faced an issue, in my sharepoint folder more than 1000 records therefore I could not find my file in the table on Power Query, do you have any idea to give me? Thanks

[@AccessAnalytic]

At 8 mins 30 I put a filter in to get to the right folder, that sounds like what you need to do

[@mariejoybarrameda]

I also experienced this one. I filtered it yet it seems it does not shows all the folder since to reached the limit ?

[@mariejoybarrameda]

is there anyway ? where I can locate the right folder ? Thank You in advance.

[@Acheiropoietos]

ok, this is getting ridiculously good now..

[@AccessAnalytic]

Glad you’re finding some useful content 😀

[@antshant1]

Thanks a lot :) you just saved me from a big embarrassment at work.

[@georgeellis5237]

Thank you for this...I thought it just me! Well done.

[@AccessAnalytic]

You’re welcome George, the battle is real!

[@SuperAlex0330]

Thank you so much! I tried to find this solution several times and I finally did it! It is hard to find it in spanish versions. Greetings from Colombia!

[@hemanoelthales280]

Thank you so much!! Really helpful!

[@AccessAnalytic]

You’re welcome Hemanoel

[@ExcelWithChris]

Thanks for all your amazing videos. I have a question regarding power query and getting data from a Sharepoint folder on web. Everything works perfectly. The issue is when I add a new file to the Sharepoint documents folder and I try another get data from same Sharepoint folder it keeps showing the original list of documents. Even if I rename a file on the Sharepoint side it does not seem to update. This is a major issue as I might be adding files to the Sharepoint documents folder in future and want to import them into the same Excel file as queries as my project develops. I have looked everywhere for a “update” button. I even tried Update All before going back into Power Query and start a new “From Sharepoint folder” query. The same original content (files) show up.

[@AccessAnalytic]

Hi, thanks for the kind comment. Is the data not pulling through when you click Refresh All? Or are you saying it doesn’t show up when in the Power Query editor window ( in which case you may need to click Refresh Preview )

[@ExcelWithChris]

Access Analytic all works fine. The issue is when for example I go: Get data From File Sharepoint folder I enter the path The next screen shows yiu a list of all documents in the folder on Sharepoint All good upto here first tine around. I then go and upload new files to the Sharepoint folder ( or even change a file name) I go through the same steps to maybe now link the new uploaded file. One I provide path again and enter, I keep getting the same list of files the first time around. Renamed/newfiles do not show up.

[@adriannacook313]

Thank you so much for your video, I could see in this video that we can connect to one particular excel file on OneDrive at a time using power query. However, I’m wondering if there would be a way to use power query to connect more than one excel files that are stored in the same folder in OneDrive for business, and every time this folder has a new file, the PQ will be updated accordingly after we click refresh? If it’s feasible, would you mind showing me how I can do this, please. Thank you very much

[@AccessAnalytic]

Hi Adrianna, yes you can. I've a video here. kzbin.info/www/bejne/Y6XGlGN-ZbFrfqc Note the key part is after you select the folder you then pick the Combine button on the next screen (generally I would choose Combine and Transform from the drop down) to give me a chance to see if I need to clean up the data at all

[@adriannacook313]

Access Analytic thank you so much for your quick reply to my questions, truly appreciate it. My apologies for not explaining my questions precisely enough. What I meant in my previous question was how to use PQ to directly connect multiple excel files in the same folder on OneDrive business (WEB app) not the desktop app, as the desktop app will have the path of C Drive (local), which will make it hard for other users to refresh data. Fortunately, I was able to figure this out and successfully connected and combined my excel files on OneDrive web. Thanks very much :)

[@robertmos8732]

@@adriannacook313 Hi Adrianna, I'm wondering if you can share how you were able to connect and combine your excel files on OneDrive? I'm exploring the very same issue now. Anything could be helpful!

[@adriannacook313]

@@robertmos8732 For sure Robert. This is how I have done to connect my excel files on OneDrive business (web version) using PQ with following steps: Open an excel file (either a blank one or an existing file) > Data > From Other Source > Blank Query > Type in: =SharePoint.Files("nswrfs-my.sharepoint.com/personal/your_domain/") > Hit enter. A list of folders in your onedrive will appear, scroll all the way to the right to see column "Folder Path" > Click on the down arrow to filter the folder that contains the excel file(s) you want to bring in or combine in PQ (tip: type your folder name in the search box) > Once you find all the files you want to combine, on 'Content column", click the double arrow icon to combine these files > Choose data tables you want to bring the data in > Click ok

[@GeorgeKlucsarits]

Good day, Wyn. Thanks for a very insightful and helpful video. I have a question: is it possible to have a workbook in SharePoint and have the query also connect to a folder in SharePoint? I've tried to get this to work but encountered some problems, some of which you covered in your video. I'm going to try again and see if I can get it to work, but if it's just not worth doing I'd like to know beforehand. My goal is to have a workbook in a shared location that can be updated with an external csv file each week and also allow another user to add a receipt number to a record when the weekly data has been processed.

[@AccessAnalytic]

Yes absolutely possible. Connect to a folder or a file using this approach

[@kushalbhosikar7277]

@@AccessAnalytic can you show to connect to a onedrive folder or google drive folder

[@judyrodbryanvicente8638]

do you have any video on how to connect the file stored in Onedrive or sharepoint?

[@AccessAnalytic]

Not sure what you mean sorry

[@letsride8840]

Excellent Demo. Thank you very much!!

[@AccessAnalytic]

You;re welcome Larry, thanks for letting us know

[@szymon3483]

Thank you a lot, I was just trying to do this but would have never thought of deleting the last part of the adress :D

[@AccessAnalytic]

You’re welcome

[@adamwilliaminglis7789]

Hi Wyn - I solved the issue with getting the path loaded, and getting the data across - however to your point at the beginning of the video [01:50] "Connecting to C: Drive version means OTHERS wont be able to refresh the query" ... This is still the case, for me, other users, with permission to access both parent and child data files cannot hit 'refresh' without getting an error [microsoft.mashup.engine.interface.resourcesaccessforbiddenexception] . frustrating as ultimately I want to update 1 file (on sharepoint or onedrive) and have all the users that connect to that one file be able to refresh their own sheets without me.

[@AccessAnalytic]

Hi Adam, I found this in one forum : "This happens on files that are shared on Sharepoint with Autosave ON. This is how I solve it everytime. Go to Powerquery File>Options and Settings>Data Source settings>Global Permissions. Delete the Global permissions and close the dialog boxes. Refresh the file again and it will ask for your credentials again and it will work."

[@adamwilliaminglis7789]

@@AccessAnalytic perfect! I came across this last night and tested it today, works a treat. It's reassuring we found the same forum. Still, a long way round for a very simple request. Thanks again!

[@lukeandchitraswygard8567]

This was so helpful for me! I do have an issue though. The query works for me, but not my colleague who actually needs to use it. :( I'm getting a Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException error. Any ideas?

[@AccessAnalytic]

I found this in one forum : "This happens on files that are shared on Sharepoint with Autosave ON. This is how I solve it everytime. Go to Powerquery File>Options and Settings>Data Source settings>Global Permissions. Delete the Global permissions and close the dialog boxes. Refresh the file again and it will ask for your credentials again and it will work."

[@daverussell450]

Thanks Wyn, as a new user to PQ I thought I was missing something, looks like I am experiencing the same problems as many others.

[@AccessAnalytic]

Nope it really is that convoluted 😊

[@isisgomez28]

THANKS!!!!!!! A SUPER LIKE for you

[@danielkogan84]

With OneDrive personal you are able to embed a file and use the link to access an individual file. That way someone outside the organization can query that file. Is it possible do the same with OneDrive for Business with files and folders? So basically not accessing your own files/folders using the organizational account but some other way to access external sources?

[@AccessAnalytic]

Not the I know of sorry. OneDrive for business is essentially SharePoint so behaves differently to OneDrive personal.

[@drury2d8]

Thank you, now that Ive managed to link it, can i access data directly with standard VBA commands?

[@AccessAnalytic]

No quite sure what you mean sorry Darrik

[@mustchoosename]

Hi, I created a query in my onedrive and everything works fine. I than shared all the folders with a colleague, but the problem is that she can't "refresh" the query due to a path error! Any advice? Thank you

[@AccessAnalytic]

Hi Pedro, do they have access to you’re one drive folder? Can they just see the folder and all the files in it?

[@ronniehiing8265]

Thanks for the video, I am trying to "From Web Method and I got this error: Unable to connect We encountered an error while trying to connect. Details: "The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

[@AccessAnalytic]

Sounds like you may be trying to connect to OneDrive personal. Check out this thread... techcommunity.microsoft.com/t5/excel/excel-quot-getdata-quot-from-file-or-web-onedrive/m-p/857687 If so possible solution here techcommunity.microsoft.com/t5/excel/excel-quot-getdata-quot-from-file-or-web-onedrive/m-p/857687

[@raa8887]

Muchas gracias. Me ha servido de mucho. Te deseo lo mejor y sigue así.

[@AccessAnalytic]

De nada

[@azharshaik21]

Thank you. How do I export the existing power query data to share point (data is coming from oracle db)

[@AccessAnalytic]

I’m not sure. Excel Power Query can load the data to a Table and then that table could be loaded to a SharePoint list. Is that the sort of thing you are thinking of?

[@azharshaik21]

@@AccessAnalytic I'm talking in terms of power bi

[@AccessAnalytic]

If you want to export power query data I’d go with Excel power query. If it’s already part of Power BI then potentially you can use DAX studio to export tables of data to csv

[@azharshaik21]

@@AccessAnalytic Thank you I will try this out

[@TheMaster9291]

Very useful info, thank you

[@tinygod81]

Question, when you set up a query for an excel hosted within a SharePoint Folder, how long does it take to reflect changes made to that specific excel? For instance, I create the Query and it loads all data as intended but then I go to the Source WorkBook, make a single change (from browser based excel), go back to the master excel and refresh the Query and do not see the change. I have tried clearing cache and still have not seen the change.

[@AccessAnalytic]

It should be instant if you are connecting the SharePoint version and changing the Excel file in the browser.

[@LapaConsult_Ex_Macraris]

useful trick thank you!

[@lucmorineau6741]

Hello Wyn, Thanks for those explanations - Itried to reproduced it two months ago and it was running well. I'm trying again those days and everyt time I received anerro message saying I do not have the access right to my sharepoint file and folder. Unfortunatly I can not find the way to have the menu you show to slect the connection /accessr igth to this Sharepoint folder. What should I do to enable the access?

[@wynhopkins4023]

Hi Luc, can you send me some screenshots to [e-mail]

[@lucmorineau6741]

FYI, I found where to clear access rigths and reload them, but it does not change the fact that i've error message telling me I cant' open the file :(

[@lucmorineau6741]

Hello Wyn, Sorry to have taken time to come back to you. I try again this week and to be honest I do not understand what has changed but it's now working again after the reset of access right within the query. The may be only difference is to go out of the the query to see if it works. Staying the Query the ERROR message was still there after Acces right modification.

[@marcosbertozzo3193]

Thanks!

[@AccessAnalytic]

You’re welcome Marcos, make sure you see my newer easier method

[@diegolozano2397]

Thank you thank you thank you.....sooo helping video

[@AccessAnalytic]

You're welcome Diego

[@supriyaranjan6945]

I'm not getting the "Copy path to clipboard" Please do let me know. Thanks in advance 🙏🙏

[@AccessAnalytic]

Maybe check out my updated video to see if that helps kzbin.info/www/bejne/rIG5Z2qIgayihck

[@ExactProBi]

I have a home O365 plan, I think I don't get sharepoint in this case, then how do I connect folders in Power Query to get data?

[@AccessAnalytic]

If syncing the folders to your computer a d connecting to those is not a good option for your scenario then check out this article exceleratorbi.com.au/loading-a-file-from-onedrive-personal-into-power-bi-desktop/

[@ExactProBi]

@@AccessAnalytic Thank you

[@YasirBai]

Please advice how to connect a "Shared" Excel file (authorised to Edit, but not owner of the file)

[@AccessAnalytic]

Not sure on that one sorry Yasir. Do you have access to the folder the file is in?

[@jacobmurray6731]

I use a folder as my source rather than a file. Is there a way to do it for the folder? I don't mind having it point to a C drive location I have connected to OneDrive, the problem is that Power Query thinks the files are in use all the bloody time because of OneDrive!

[@AccessAnalytic]

Hi Jacob, check out my reply to Pascal in the comments. If you’re happy to connect to the C drive then just use the Get Data from Folder option instead of From SharePoint folder - thus is much simpler. Beware that others won’t be able to refresh the file since it’s pointing to your c:drive

[@m1cannas]

I run also into a lot of little painful moments. I'm stuck with parsing character filter values in an Excel ifraime URL. Tips are always welcome

[@AccessAnalytic]

The Excel Community is a good place to post questions with screenshots techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral

[@lorenaqueiroz4696]

Hello Wyn, I have Microsoft 365 Business Standard and the option "From SharePoint folder" is missing from my Power Query. Do you know how can I enable this function on my Excel? Thanks in advance.

[@AccessAnalytic]

Hi Lorena Queiroz , unfortunately that version does not come with the SharePoint connector, which makes no sense in my view

[@lorenaqueiroz4696]

@@AccessAnalytic Thanks for the attention. Doesn't make sense to me either. Can you tell me which versions come with the SharePoint connector?

[@AccessAnalytic]

Here you go...support.microsoft.com/en-us/office/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16

[@lorenaqueiroz4696]

@@AccessAnalytic Thank you very much!

[@ExcelExciting]

Will not give permission errors if someone else try’s to refresh the query for data placed on OneDrive Business/Personal. I tried by downloading the file & took the download link.. but notice that link is valid for 24 hrs only.. I’m looking solution like we have on SharePoint.. just connect & you are always there..

[@AccessAnalytic]

You'd need to share the underlying File / Folder via the Share Option in OneDrive / SharePoint. The only date restriction I'm aware of is the expiry date you can set when sharing with external users (I think this may be influenced via the admin settings)

[@JuliohectorDeocaresjdeocares]

OK, but that is only for sharepoint, I try for one drive personal account and the only way is catching the web download link, but the issue is that download link, It is not a permanet acces because is changing all the time.

[@AccessAnalytic]

Hi Julio, yes I haven’t seen a solution for OneDrive personal

[@JuliohectorDeocaresjdeocares]

@@AccessAnalytic I think I found the solution, if it works I answer you with a video.

[@jonathanacaro9792]

Instead of connecting to an excel file, is it possible to connect to .mdb one?

[@AccessAnalytic]

I don’t think so but I’m not 100% sure on that

[@southy474]

Thanks Wyn!

[@tobiassimon7696]

I can't seem to connect the query to my onedrive shared with me folder. Anyhelp on how to do that?

[@AccessAnalytic]

Is it OneDrive for Business? Can you see the folder and contents if you just navigate to it via the link? Check out this video also to see if that helps kzbin.info/www/bejne/rIG5Z2qIgayihck

[@tobiassimon7696]

@@AccessAnalytic No it's just a regular Onedrive, If I follow the link from email it takes me straight to excel online and into the workbook. I can access it through the shared folder in onedrive also, but I can't get the onedrive shared folder to appear in excel when trying to query. I can get other folders to though just not the "shared" folder. I'll watch the video and get back to you. Thanks

[@AccessAnalytic]

@@tobiassimon7696 my approach is just for OneDrive for Business and SharePoint. OneDrive Personal is a different setup. Check this out exceleratorbi.com.au/loading-a-file-from-onedrive-personal-into-power-bi-desktop/

[@wandersonnogueira9828]

Thanks a lot

[@AccessAnalytic]

You're welcome Wanderson

[@Thor_Underdunk_Caballerial]

There isn't an icon alongside the path under details to copy, anyone have any suggestions? Could it be because I'm using a home user OneDrive account rather than professional?

[@AccessAnalytic]

Yep this is only for SharePoint / OneDrive for Business

[@Thor_Underdunk_Caballerial]

@@AccessAnalytic Thank You for super quick reply. I did find a workaround by using the OneDrive desktop app. All the best!

[@andrewcragg420]

There's a quick fix for individual files, but not folders: select the file in SharePoint, go to details and copy the file path from the bottom of the pane. Then you can you that with connect to workbook or web doesn't matter which, it will recognise the URL and make it a web link to the SharePoint file

[@AccessAnalytic]

Good call Andrew, that is a good tip

[@andrewcragg420]

@@AccessAnalytic Thanks, still struggling to combine and transform from a folder in SharePoint though - other than learning to write the code I don't know how else to do it. It is very frustrating - I've voted!

[@andrewcragg420]

Found a workaround: Transform your data from SharePoint root, and navigate the folders like you suggested, then in the tab that has the Binary field in it you should see a button with two arrows pointing down - this will combine the files in the browsed to folder!!!!!

[@AccessAnalytic]

You may find my presentation here interesting... few extra tips kzbin.info/www/bejne/aXrKf3uDfJyjeJI

[@andrewcragg420]

@@AccessAnalytic Great, thanks!

[@duncan2254]

I don't have "from sharepoint folder" option, on Get Data

[@AccessAnalytic]

Unfortunately some versions of Office 365 don’t have that connector. I think this is crazy and have given that feedback to the Excel Team. Please click on File - Feedback and let them know your situation.

[@NuffleReza-H]

When I try to link the query it says access forbidden, however the person who created that online sheet gave me the access to edit as well

[@AccessAnalytic]

I believe you need to be added as a member of the SharePoint site to be able to get access (although I'm still exploring ) Also check out the conversation with Adam William Inglis in the comments below

[@frankmedrisch7451]

When I do it to a folder for which I am not the owner it says I do not have permission to do this...

[@AccessAnalytic]

Do you have access to the folder directly in One Drive / SharePoint?

[@kushalbhosikar7277]

What if we have multiple files in one drive that we want to import

[@AccessAnalytic]

If OneDrive for business then it’s exactly the same process. If it’s Personal OneDrive then I don’t think it’s possible

[@timclifton2001]

When I click on Get Data there is no option to get data from a SharePoint Folder. How do I enable that option?

[@AccessAnalytic]

Hi, if using Excel, it may be that your version doesn't have that connector. It's only recently I discovered this, and have complained to Microsoft about it. What version of Office do you have? Business ? Business Premium? Something else?

[@timclifton2001]

@@AccessAnalytic 365 Business Standard, Excel Version 2005 (Build 12827.20336). I think its just changed in a recent version though as I swear the options were available late last/ early this year.

[@AccessAnalytic]

@@timclifton2001 , unfortunately it looks like that's missing from the Business Standard edition, which I think is madness support.microsoft.com/en-us/office/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16

[@timclifton2001]

@@AccessAnalytic That is a shame. Would it still work if I added the power query expression in a blank query?

[@judyrodbryanvicente8638]

how to manage this error ''Details: "Access to the resource is forbidden.''

[@AccessAnalytic]

I found this in one forum : "This happens on files that are shared on Sharepoint with Autosave ON. This is how I solve it everytime. Go to Powerquery File>Options and Settings>Data Source settings>Global Permissions. Delete the Global permissions and close the dialog boxes. Refresh the file again and it will ask for your credentials again and it will work."

[@fiorenceling2557]

i cannot connect to onedrive, the error message show "Unable to connect, Access to resource is forbidden". i try the function "from web" and "from sharepoint folder", both got the same error message, may i know how to fix it?

[@hariyantowidjaja6167]

Yes i have this problem too, how to fixed it?

[@lumo571]

The problem occurs when your windows credentials do not match the onedrive/sharepoint credentials (maybe private pc in office environment?). In data source seetings click edit permissions and set the type to organization.

[@alexj8117]

There has been discussion on using Api Version parameter value=14 instead of 15 for SharePoint.Files(). community.powerbi.com/t5/Issues/SharePoint-Folder-Connector-fails-for-API-15/idi-p/331819 Is that applicable in this case?

[@AccessAnalytic]

Not come across that issue. That is quite an old post you're referencing so not sure if it is a problem?

[@alexj8117]

@@AccessAnalytic not currently a problem- I’m not using SharePoint as a data source. Thanks.

[@N3V3L]

Thanks Wyn! Lifesaver indeed. When you then run into the error "Access is forbidden" follow the instructions on this url. I post it because it happened to me and added another 30 mins to my time. social.technet.microsoft.com/Forums/office/en-US/d4f50004-8fc6-4a2c-bae5-5f5787d52bf5/issue-with-excel-import?forum=excel

[@AccessAnalytic]

Is that different to what I do at around 10:36?

[@RichardJones73]

Fast forward 3 years, and it's still the same process!

[@AccessAnalytic]

Sadly yes, although this technique for finding the URL is different kzbin.info/www/bejne/rIG5Z2qIgayihck

[@RichardJones73]

@@AccessAnalytic ah I've seen this video last week funnily enough. It didn't seem much different to this video but thanks for sharing!

[@AccessAnalytic]

Yeah, just the method for getting the URL

[@ikaconstruction1775]

[@AccessAnalytic]

❤

[@developerglobe]

I followed the exact steps in the video for accessing a .xlsx via Web method by copying URL and removing the ?web=1 in the URL string; the file on OneDrive for Business has share settings to allow access only to those in my organization. I have no issue connecting to the file on my machine, but when I share with others in my org, they receive an error "[DataSource.Error] Web.Contents failed to get contents from ...[URL]... (404): Not Found" In this case, the URL is no longer my original URL set in the connection settings, but instead has been modified to my top level Sharepoint_folder_URL. com/_api/web/getfilebyserverrelativeurl('original connection URL')/$value. Is anyone else seeing this?

[@AccessAnalytic]

Haven't come across that issue. I'd suggest posting it to techcommunity.microsoft.com/t5/excel/ct-p/Excel_Cat

[@bvaishakachar1234]

im having the same issue

[@AccessAnalytic]

Try changing API Version = 15 to API Version = 14 Check out this post community.powerbi.com/t5/Issues/SharePoint-Folder-Connector-fails-for-API-15/idc-p/586800#M36302

[@ryan_chew97]

the size of your QAT bar scares me.

[@AccessAnalytic]

The AA Ribbon? 😄 or just the QAT?

[@marcinzajac389]

Is this a joke? this is really how we connect to files on onedrive???

[@AccessAnalytic]

There’s an easier but still not easy way shown in a more recent video kzbin.info/www/bejne/rIG5Z2qIgayihck.