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
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 RUclips video that talks about it. But really thankful that you shared your video here. Million thanks!!! :)
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!
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.
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.
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.
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. 👍
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
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 😃
@@diegolozano2397 here you go ruclips.net/video/vPV67RLGoOg/видео.html and there's a related playlist here ruclips.net/p/PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un
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.
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
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!
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
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
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!
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
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
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. ruclips.net/video/nPlrQUbEn4o/видео.html
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...
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?
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
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
Hi Adrianna, yes you can. I've a video here. ruclips.net/video/-tcc0H0L8HE/видео.html 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
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 :)
@@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!
@@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
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.
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 )
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.
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.
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?
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
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?
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."
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/
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?
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.
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.
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."
@@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!
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.
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.
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."
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
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!
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
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?
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 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!
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!!!!!
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 ruclips.net/video/vPV67RLGoOg/видео.html
@@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
@@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/
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..
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)
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.
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.
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
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."
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?
@@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.
@@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
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?
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
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?
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.
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?
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
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
Cheers, you’re welcome Martín 👍🏼
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!
You’re welcome Harry. Thanks for letting me know it helped.
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 RUclips video that talks about it. But really thankful that you shared your video here. Million thanks!!! :)
You're welcome Jason. Not sure it's possible with OneDrive personal. Glad it helped though
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!
Cheers Scott, make sure you check out my updated version 😀
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.
You’re welcome. Thanks fir letting me know it helped you
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.
Glad to help, it’s a very hidden process. Make sure you check out the updated easier version of my video I did recently
I've been stuck here for a few weeks. Thank you so much!!!
Glad it helped you Summa. Thanks for letting me know
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.
Great!
Make sure you check out my newer videos on this too
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.
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.
love your text commentary on random lol moments throughout the video
Thanks Nathan
Great tips on getting sharepoint folder path! I had the problem but luckily found your video. Thank you!
Glad it helped you Lingjing
Thank you so much for sharing !
You're welcome Nadege
Make sure you watch my newer version ruclips.net/video/vPV67RLGoOg/видео.html
Thank you! Really help me and my colleagues
Great, make sure you watch the updated version of this video too ( link in the description )
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. 👍
No worries Al
Thanks for the video, it helps a lot !
I gonna go with the first method
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
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 😃
Thanks for voting !
Thanks very much, really helping ! Still wondering how to setup permissions on the source, if this query is refreshed by another user ?
Need to share the source file or folder with the end user
Thanks Wyn , it's very useful steps for me
Glad to help
Thaks a lot your video has been soooo usefull. a big hug from colombia
Thanks Diego, make sure you take a look at the updated version of this video. Thanks for leaving a comment.
@@AccessAnalytic can you sendme the url of the new video please. 🙏
@@diegolozano2397 here you go ruclips.net/video/vPV67RLGoOg/видео.html and there's a related playlist here ruclips.net/p/PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un
This is brilliant! Thank you for this.
Thanks Julie, make sure you check out my even easier way to find the URL link ruclips.net/video/vPV67RLGoOg/видео.html
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.
Make sure you check out my updated video here: ruclips.net/video/vPV67RLGoOg/видео.html
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
Thank You for your persistence Wyn, just what i was looking for.
No worries Nigel
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!
This video is GOLD!!! Thanks a lot for sharing
Thanks Uwe
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.
Nope it really is that convoluted 😊
Thanks a lot :) you just saved me from a big embarrassment at work.
Great explanation! Thank you very much for this!
Glad it was helpful, thanks for letting me know Frank
ok, this is getting ridiculously good now..
Glad you’re finding some useful content 😀
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
At 8 mins 30 I put a filter in to get to the right folder, that sounds like what you need to do
I also experienced this one. I filtered it yet it seems it does not shows all the folder since to reached the limit ?
is there anyway ? where I can locate the right folder ? Thank You in advance.
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
Check out this video Hasan: Combining Multiple Files from a folder using Power Query in Excel or Power BI. ruclips.net/video/nPlrQUbEn4o/видео.html
🙏
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!
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
Thank you for this...I thought it just me!
Well done.
You’re welcome George, the battle is real!
Thank you so much!! Really helpful!
You’re welcome Hemanoel
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
You’re welcome
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
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.
ruclips.net/video/nPlrQUbEn4o/видео.html
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...
I am based in Perth, Australia. Originally from Wales. Maybe check out Alan Murray www.linkedin.com/in/alanmurray-computergaga
@@AccessAnalytic Ah amazing, thank you so much!! (Sorry for nearly accusing you of being English...!!)
Excellent Demo. Thank you very much!!
You;re welcome Larry, thanks for letting us know
Thank you. How do I export the existing power query data to share point (data is coming from oracle db)
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?
@@AccessAnalytic I'm talking in terms of power bi
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
@@AccessAnalytic Thank you I will try this out
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
Hi Adrianna, yes you can. I've a video here. ruclips.net/video/-tcc0H0L8HE/видео.html 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
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 :)
@@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!
@@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
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.
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 )
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.
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.
Yes absolutely possible. Connect to a folder or a file using this approach
@@AccessAnalytic can you show to connect to a onedrive folder or google drive folder
Muchas gracias. Me ha servido de mucho. Te deseo lo mejor y sigue así.
De nada
do you have any video on how to connect the file stored in Onedrive or sharepoint?
Not sure what you mean sorry
THANKS!!!!!!!
A SUPER LIKE for you
Thanks!
You’re welcome Marcos, make sure you see my newer easier method
Thank you, now that Ive managed to link it, can i access data directly with standard VBA commands?
No quite sure what you mean sorry Darrik
Thank you thank you thank you.....sooo helping video
You're welcome Diego
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?
Not the I know of sorry. OneDrive for business is essentially SharePoint so behaves differently to OneDrive personal.
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
Hi Pedro, do they have access to you’re one drive folder? Can they just see the folder and all the files in it?
Very useful info, thank you
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?
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."
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?
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/
@@AccessAnalytic Thank you
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?
Hi Luc, can you send me some screenshots to whopkins@accessanalytic.com.au
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 :(
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.
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.
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."
@@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!
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.
It should be instant if you are connecting the SharePoint version and changing the Excel file in the browser.
useful trick thank you!
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.
Hi Lorena Queiroz , unfortunately that version does not come with the SharePoint connector, which makes no sense in my view
@@AccessAnalytic
Thanks for the attention. Doesn't make sense to me either. Can you tell me which
versions come with the SharePoint connector?
Here you go...support.microsoft.com/en-us/office/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16
@@AccessAnalytic Thank you very much!
I'm not getting the "Copy path to clipboard" Please do let me know. Thanks in advance 🙏🙏
Maybe check out my updated video to see if that helps ruclips.net/video/vPV67RLGoOg/видео.html
Please advice how to connect a "Shared" Excel file (authorised to Edit, but not owner of the file)
Not sure on that one sorry Yasir. Do you have access to the folder the file is in?
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."
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
Thanks a lot
You're welcome Wanderson
Thanks Wyn!
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!
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
Instead of connecting to an excel file, is it possible to connect to .mdb one?
I don’t think so but I’m not 100% sure on that
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?
Yep this is only for SharePoint / OneDrive for Business
@@AccessAnalytic Thank You for super quick reply. I did find a workaround by using the OneDrive desktop app. All the best!
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
Good call Andrew, that is a good tip
@@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!
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!!!!!
You may find my presentation here interesting... few extra tips ruclips.net/video/3IgNHMG7pB0/видео.html
@@AccessAnalytic Great, thanks!
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
The Excel Community is a good place to post questions with screenshots techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral
I can't seem to connect the query to my onedrive shared with me folder. Anyhelp on how to do that?
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 ruclips.net/video/vPV67RLGoOg/видео.html
@@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
@@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/
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..
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)
I don't have "from sharepoint folder" option, on Get Data
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.
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.
Hi Julio, yes I haven’t seen a solution for OneDrive personal
@@AccessAnalytic I think I found the solution, if it works I answer you with a video.
Fast forward 3 years, and it's still the same process!
Sadly yes, although this technique for finding the URL is different ruclips.net/video/vPV67RLGoOg/видео.html
@@AccessAnalytic ah I've seen this video last week funnily enough. It didn't seem much different to this video but thanks for sharing!
Yeah, just the method for getting the URL
What if we have multiple files in one drive that we want to import
If OneDrive for business then it’s exactly the same process. If it’s Personal OneDrive then I don’t think it’s possible
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
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
When I do it to a folder for which I am not the owner it says I do not have permission to do this...
Do you have access to the folder directly in One Drive / SharePoint?
how to manage this error ''Details: "Access to the resource is forbidden.''
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."
When I click on Get Data there is no option to get data from a SharePoint Folder. How do I enable that option?
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?
@@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.
@@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
@@AccessAnalytic That is a shame. Would it still work if I added the power query expression in a blank query?
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?
Not come across that issue. That is quite an old post you're referencing so not sure if it is a problem?
@@AccessAnalytic not currently a problem- I’m not using SharePoint as a data source. Thanks.
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
Is that different to what I do at around 10:36?
the size of your QAT bar scares me.
The AA Ribbon? 😄 or just the QAT?
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?
Yes i have this problem too, how to fixed it?
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.
❤
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?
Haven't come across that issue. I'd suggest posting it to techcommunity.microsoft.com/t5/excel/ct-p/Excel_Cat
im having the same issue
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
Is this a joke? this is really how we connect to files on onedrive???
There’s an easier but still not easy way shown in a more recent video ruclips.net/video/vPV67RLGoOg/видео.html.