Get Data From SharePoint or OneDrive with Power Query - Demystified!
ฝัง
- เผยแพร่เมื่อ 1 ต.ค. 2024
- In this video I cover the 3 options for getting data from SharePoint or OneDrive with Power Query:
00:27 From a Single File on SharePoint or OneDrive
04:08 From a SharePoint or OneDrive Folder
08:12 From a SharePoint Shared Library
11:56 Sharing Files with Others
Note: The SharePoint Folder connector is only available in Excel 2019 Professional Plus and with a Microsoft 365 Apps for Enterprise license
Get step by step written instructions here: www.myonlinetr...
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy
![ลองสั่งของจากแอพ temu ถูกจริงหรอ?? [โกงไหมครับ ep.88 ] | DOM](http://i.ytimg.com/vi/RTcZSQTiDZw/mqdefault.jpg)
You are my heroine, that I searched for long time and no one at work had a clue.
So pleased I could help, Markus :-)
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
Wow, thank you so much!
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
Thanks for your kind words, Martin!
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.
I agree, it could be much easier!
This tutorial saved my job!! I don't have enough words to say THANK YOU SO SO MUCH!!! You have blessed my life 😇
Wow, that's awesome to hear, Javier!
Mynda, can you update the tutotial? I saw that microsoft updatet online folders link. We dont have "/layou" anymore and the tutorial for folders isnt working. Can you save us, please? Greats from Brazil!
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.
Yes, I agree it could be easier.
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
Sounds like you have a version of Excel that doesn't have the right connector.
Lots of tutorials, none as simple and easy to follow like yours. Thanks a lot!
Thanks so much!
I got it, thank you, at 2:45 i choose Organizational account tab for logging in cuz share point file is in limited access.
I was trying to figure this out for a week until i came across your video. Youre a life saver.
Glad I could help 😊
Excellent! This came a the most appropriate time because I have been struggling with this.
So pleased to hear that, Patrick!
Hello!
I'm trying to follow the video but I noticed that the "select from sharepoint folder" button is missing from my excel.
Do you know why? What can I do?
I am using Microsoft 365 with business premium license.
What would cause a user to not see the "From SharePoint Folder" option under the "From File" pop out menu?
You don’t have the appropriate user license.
@@MyOnlineTrainingHub What are the user licenses that are needed?
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?
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.
@@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.
@@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.
Yep, it's tedious!
Mynda, Thanks for another really useful video. In the example for getting data from a single file, you ended up opening the file in Excel but from OneDrive on the web. I understand this was to illustrate the different paths to the file. Forgive me for not testing this, (as I'm still watching), but do you have to open the workbook from the Web to get the correct path visible in Excel, or would it work the same way if you had opened it from OneDrive on your desktop?
Thanks, Simon! Yes, you must open the file from OneDrive on the web in order to get the correct path to the file.
Thanks, It's helpful, can you share how to generate reports from multi lists in sharepoint with excel?
Hello! I do not see Copy Link option when I right click on my file in OneDrive. Can you please help??
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.
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
Thanks for sharing solution that works 🙏 why MS made it so complicated?
Who knows! Hopefully it will be easier in future.
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.
The SharePoint connector is only available in Excel 2019/2021 Professional Plus and with a Microsoft 365 Apps for Enterprise license.
The video I was looking for the last few days. Thank you a ton, Tracy. Keep Rocking.
Glad it was helpful!
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!!
Glad it helped, Lauren!
what a life saver, can't believe is 2023 and there is no easy way to connect to sharepoint on office business standard, how do you come across this workarounds
Glad I could help. Workarounds come from all sorts of places. In this case it was from another Microsoft MVP, but sometimes it's Microsoft themselves, forums, other users and trial and error.
Hi... When in Access Web Content, can I use anonymous?
Now, The dialog box Access Web Content did not appear...
We encountered an error while trying to connect
Details: "The downloaded data is HTML, which isnt the expected type. The URL May be wrong or you might not have provided the right credentials to the server."
The issue is described in the error message. You are providing it with a .html link which is not accepted.
Hello, thank you for posting. I have a question. Since I am using a link from the sharepoint if I modify the file within the query will this be modify also in the sharepoint route?
Power Query doesn't/can't write back to the source files, so any editing you do in the query does not affect the original file.
Thank you for the great content! Like other viewers, I've referenced this video several times when trying to extract data from a Sharepoint list. While it isn't explicitly covered, the principles are the same. In the process I've discovered that not every field in a list will come across in the extract. Most frustratingly, this happens to a field that contains a name that resolves to an entry in the Active Directory Global Address List. I've created a workaround but would be open to hearing about any solution to this.
I've not come across that problem before, Tom. Hope you find a workaround.
@@MyOnlineTrainingHub thanks. I would never rule out that it is something I am doing :) but it remains a mystery to me.
I'd probably give up and move the file to a location that's easier to navigate to 😂
Useful video solved my problem! How about I am using excel getdata from a folder in sharepoint? My dataset are combined from 100+ files that save in one sharepoint folder.
Glad it was helpful! In theory, you can get data from your 100+ files, but I have no idea about the performance impact that many files would have. You'd have to test it.
It seems that import from Sharepoint Folder is not available for Excel 365 Business but for Enterprise edition or Office 2019 only :-(
Do you have any idea if there is any solution for Office 365 Business?
Yes, it's a bit annoying that the connector is not available to all. I don't have a workaround other than to save the files somewhere else, like OneDrive..
Hi Mynda, this for overview. I have noticed that there seems to be an a videos on TH-cam 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?
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.
Hi. I’ve set up file path using one drive, everything works for a couple of week. Had to complete a duo mobile and VPN update for works system. After this update refreshing data failed. Error message. Is this a common problem after system updates.
I've not heard of this issue before, Martin. The link to OneDrive/SharePoint shouldn't be affected by your VPN AFAIK.
Thanks for the help, it's a very good video.
Could you please explain more how other users can update the same consolidated file, since I have tried and still have not been able to get other users from the same corporation to update it?
this is so that different users can update the same (consolidated) file containing the relationships and connections
Sounds like a permission issue. Not something I'm very experienced in, but if you have SharePoint then I'd reach out to your IT admin and ask them to check if they have a setting restricting file access.
I love you Mynda, I spent a lot of time trying to go to a specific location in the Sharepoint and combine its files, I tried several ways and I was also trying to filter, but I was not doing it the right way, funny thing I had already watched this video, it seems I missed that part then.
Just a quick question if you don't mind, will my query see the new files without the need to manually filter again? Thanks a lot!!!
Glad it was helpful, Oscar! Yes, as long as you add your new files to the same folder, Power Query will pick them up on refresh.
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?
Thanks for the reply both personal Onedrive and work Onedrive files are synced to hard drive with AutoSave On. Just found it interesting that I can have work Onedrive files open in excel on either the desktop or browser open and still sync the related query but not on personal Onedrive.
Was just interested to see what your and other users experience was to see if this was just something I was experiencing and others not.
I can't have ODB or ODP files open and refresh queries.
How can you get data from a shared folder on OneDrive? You went over the shared library on SharePoint, the individual files on SharePoint and OneDrive, but I need to figure out how to query shared folders on OneDrive. Thank you!
At 4:09 in the video you'll notice that I am actually getting data from a OneDrive folder. The process is the same as a SharePoint folder. However, it's only available with OneDrive for Business.
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
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.
Hello! Thank you so much for the video. It works. However, I have a report published and it is automatically refresh but it seems to have problems refreshing the Sara form the folder because it is attached to my user. Is there any way to fix this ?
You need to give whoever is refreshing, permission to the file.
I do not understand why Microsoft doesn't just allow us to point directly to the folder we have a gazillion files in our SharePoint, I am trying to filter to the folder but the query is not finding it, I have copied and pasted the exact path but still no go, is there any other way to reference the folder.
Instead of using the exact path, use the filters to find the folder or a folder higher up the hierarchy, thus reducing the list size and making it more manageable. You may need to filter in steps if you have a lot of folders.
hi there..I got problem with the sign in part using organizzational account..It says that "something when wrong"..i had try multiples time..same problem occur..would you mind to help me?
I'm not equipped to help with sign in issues. Please reach out to your IT people who should be able to help you.
Hi, Mynda. I don't have OneDrive for Business (Enterprise) in my organization? Does this work where each of the users are using free OneDrive accounts?
No, as stated at the beginning of the video, unfortunately, this is only available for OneDrive for Business or SharePoint.
How can I do this but with an image instead of .xlsx file? I need to get the folder path of an image storaged in my personal One Drive, and if possible, to automatically change that folder path when the image is reorganized between folders. Thanks!
You can use Power Query to get a list of file names the same way. Just stop at the step where you see the file names.
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?
The SharePoint connector is only available with a Microsoft 365 Apps for Enterprise license. You would have to upgrade your 365 license.
HI, this is amazing, thank you. Neeed a small help please. One of my team members is not able to sign in thru share point, to run the query. He is missing the organizational account option, no one has been able to help us. Anything you can think off to help us
Glad it was helpful! I'm not a SharePoint expert, so I'm not sure what your colleague's problem would be, sorry.
Greate video, but when we have many folders it's hard to find files form specific folder. Is there any trick to get the files from folder?
You can filter the column containing the folder names, but personally, I make a new folder that's not buried so it's easy to navigate to!
HI, Great Explanation
I need help regarding that after login MS account its showing error for the below, how to resolve it please let me know, thanks
We could not authenticate with the credentials provided
Sounds like the username and or password were possibly incorrect.
Hi , at 6:57, i have 46 files different format I do not want to combine and merge but I want to open all in different sheets, but "06:57", I m l clicking at Binary and it can open one file and when i click on second file "binary" its replacing the first one, any suggestion
The only way to open multiple files in separate queries/sheets is to do it one at a time.
Hello, great info but what about 365 business users , seems like Microsoft left out the SharePoint connector out , is there any work around? Thanks
No workaround, sorry. It's frustrating and we MVPs tell Microsoft all the time!
This is great. I'm trying to link to a sharepoint folder but when I click File, there is no option for Sharepoint folder. Any idea what the problem is?
The problem will be licensing. You need to have Excel 2019 (or later) Professional Plus or a Microsoft 365 Apps for Enterprise license.
How get load dynamic 365 data in Excel through web query
For OndDrive personal edition there is a way of grabbing data using the 'embed' option.
th-cam.com/video/lGpwsX57mQk/w-d-xo.html
A link with sound th-cam.com/video/Ka8S-PnGMGc/w-d-xo.html
The link seems to break once I replace the file with an updated version in onedrive. Do you have a solution for how to use onedrive for files that need to be updated in onedrive/sharepoint on a daily basis?
If you have the file synced to your hard drive to OneDrive, then you can replace it on the hard drive and it won't generate a new link in OneDrive.
Hey, thanks! great video. I just have one question, when I try to open the file with a data source in the browser I am not able to refresh the data. Is it possible to use power queries in excel online?
Not yet, but its coming.
How can we make the SharePoint path dynamic. I know this possible for local folder but not getting this done for SharePoint.
Probably, but it depends on your scenario and where you'll get the information for the dynamic path.
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?
I would have to give Phil permission to the file in OneDrive/SharePoint for him to refresh the query.
I have a question... once I load the data.... and share the report.... if I am opening the report after 1 month... it is asking me to give share point credentials in query editor... is there a way to get that in excel view than query.. so that the end user do not open the query editor...
You can edit the credentials via the Data tab > Get Data > Data Source Settings menu in Excel.
my SharePoint is way too big and it take me forever to apply filter and it's never success to drill down into my target folder. :(
Mine is too, so I create a folder closer to the top of the folder structure so it's easy to find and put my files in there.
Hi Mynda, may I check if possible to get data if my files are saved on company's intranet? Thanks in advance
You'd have to download the files to a folder first. Unless your company's intranet has an API you can tap into. Here's a video on getting data from API's with Power Query: th-cam.com/video/2mR44X7PrRo/w-d-xo.html
I don't see the 3 buttons when I click on "Info", so I don't see "Copy path". What gives?
Probably a version difference. I am using 365.
Great Video. But I don't have Sharepoint Folder On Microsoft 365. What should I do ?
Thank you! SharePoint folder connector is only available with a Microsoft 365 Apps for Enterprise license. You would have to upgrade your 365 license.
This is a crazy solution for a standard user. Btw my url looks completely different and cant be used. No idea why
Yeah, it could be easier.
You forgot to mention that this is only work if you have office 365 for entrerprises, I have for Business so the option to select from sharepoint does not even exist
Yes, unfortunately. I had put a note in the video description clarifying that.
If another user in your organization or outside your organization opens this spreadsheet, will they need to authenticate their credentials when they try to load the data source?
Yes.
its not working for me :( im trying to pull an excel file from sharepoint. Followed the steps but says link is incorrect
Sorry to hear that, Dan. I'd double check the link in relation to my tutorial.
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?
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.
I tried importing data from sharepoint to power query but my dates are all offset by 1 day. How do I solve this?
That's very odd, Sam. Are the dates generated using the TODAY function? Maybe the date on the SharePoint server is in a different time zone to you.
Great video! Thank you! QQ - is it possible to do something similar to get the library folders, sub folders, files within the sub folders and the custom columns they are categorised under? EG, Status, Assigned to etc. I want a view of the various doc in my library and their classifications but cant seem to find a solution. I can of course export directly out of sharepoint but trying to automate.
Any help appreciated :D
Good question. I'm not sure about getting the metadata from SharePoint. It looks like it brings in some information like Date Created, Date Modified, so you may be able to get this other information too.
@@MyOnlineTrainingHub I think I have found a relatively good solution. The export to excel function in SP actually runs as .iqy. So this can be connected to an excel file for each section of the library needed. Granted, I can only refresh the data upon opening the excel file, but might be able to get around this with PowerAutomate! 😄
crisp, clear and to the point. Much appreciated
Glad it was helpful!
Hello. I've got a problem and would love to hear from you what are my options. In my company I've got Microsoft 365 Business which means I don't have available option Get Data->From SharePoint folder. Is there any way that I could get data from SP folder whilst having Microsoft 365 Business subscribtion?
I'm not aware of a workaround if you don't have the right license, sorry.
@@MyOnlineTrainingHub I did it :D
Awesome tip!!!
Thank you, I've been trying to do this for months.
Happy to help, Michael!
Would you be able to tell me why once in a while I am getting error message as it can't connect to the OneDrive workbook and refresh the data?
A box pops up "Access Web content" with some options (e.g. Anonymous, Windows, Basic, Web API, Organization account). The issue seems to be this workbook forgets the URL link to the file on One Drive and asking me to sign in again and again. All looks good but not sure this is keep happening and to fix it once for ever. Thanks
Not sure, sorry.
@@MyOnlineTrainingHub thank you
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!
So pleased it was helpful, Joshua!
Hi @MyOnlineTrainingHub. Do you do consulting for this type of Excel work?
Hi Rich, no, however if you get in touch via email I can recommend someone who can help you: website @MyOnlineTrainingHub.com
Hi, Excellent work on it. May I ask if can I connect to someone's sharepoint folder that has been shared to me including the sub-folder and sub files. Because here you have query it successfully as it is your own sharepoint folder but what if it is not your sharepoint folder? Makes sense?
Additional on it, I want to query also the version history of the sharepoint folder and files which I do not own but has just shared to me. Thanks!
Hi John, you can certainly refresh queries authored by the owner of the folder if they've given you permission, so I'd expect it you have permission to view the folder and get the necessary links, then you could also author the query yourself.
@@MyOnlineTrainingHub Hey, I tried it using Get data>SharePoint Folder but I it continue to connect even if I have successfully sign in. Can you please show us how? I appreciate your feedback!
Thanks!
Hi, My excel doesn't have from share point folder option. How to enable it? Please help.
You would have to upgrade to a version of Excel that it's available in.
Hi, do you prepare for the examination of Microsoft Excel Expert ? or have any training for it ?
Thanks for your interest in my courses. My Excel Expert will give you a sound foundation for the exam, but I can't guarantee it'll cover all topics required. You can see the syllabus for my course and compare it to the exam requirements here: www.myonlinetraininghub.com/excel-expert-upgrade
Works great and did solve my problem. The only problem I have now is that it takes around 1 hour to scan and filter the proper files, there are only 300k records coming back but it is all the overhead on my OneDrive. I am thinking that I can ask IT to allocate a dedicated OneDrivethat I can use for this application and potentially other similar use cases, where it is not competing with 2TB of other files.
Update: I was able to put my data in a teams site, and since there are only a few files (lots of folders) it was very fast, under 20 seconds to run the Dax. Thanks so much!
Wow, awesome to hear, Don!
I am trying to use power query to connect to excel files stored in Sharepoint online. My Excel is part of Office Standard 2016, and the web connector does not behave as shown in the video, and it seems to fail. What versions of office is this known to work with?
The SharePoint Folder connector is only available in Excel 2019 Professional Plus and with a Microsoft 365 Apps for Enterprise license.
@@MyOnlineTrainingHub Thank you for the response (and the video). Can you clarify for me... Is the folder connector what is used to connect to a single document in SP?
Hey! Thanks for your helpful videos.
My 'combine files' button is greyed out - do you have any idea why this could be?
For clarity:
- I am trying to merge 3 files.
- the files are stored in an online sharepoint/mysmartplace library
- I have followed the second option in your walkthrough (get data from SharePoint folder, filter down to the file sources I need)
- I merged followed these steps by selecting 'get data' in PowerBI, rather than excel.
Huge thanks if you can help,
Ben
Not sure what would be causing that, Ben. You can try posting your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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" 😂😂😂
😆 it’ll be worth it!
What about us who has a 64 bit Win11 an no meny for SharePoint Files og folders?
You can use the Get Files > From Folder connector to consolidate files on a network or PC hard drive.
Thbak you very much for this tutorial Mynda. This is exactly what I was looking for. However, everything seems to be working for me, and it lets me get my data from my OneDrive folder from my organization, and Power query runs smoothly. But when I share the file and folder with my coworker, and he tries to run the query, he encounters an error that says "[Expression.Error] There weren't enough elements in the enumeration to complete this operation" And this appears no matter what we do. Do to know why this happens? It works perfectly for me but not for my coworker.
Have you given your co-worker permission to open the file the query is referencing?
Hi Mynda. Previously I gave up getting data from "SharePoint > from folder" as it takes a long time to even filter the required folder location from power query. So I just chose to get data from my OneDrive as it works faster. However, it limits to just myself to refresh the data. I've re-watched this video getting data from SharePoint and have learned that I'm filtering the folder incorrectly before. I need to have my team refresh it aside from myself so I need to connect it from SharePoint. However it takes a very long time to refresh the query. Do you know why? Thanks.
Hard to say why it would be slow to refresh without seeing the query, sorry. You're welcome to post your question and sample Excel file on our forum where someone can possibly help you further: www.myonlinetraininghub.com/excel-forum
Hi, at 1:50, my does not have the "Share", "Copy Path", or "Open File Location" choice above the "Protect Workbook" button as yours does. I have the Microsoft Office 365 ProPlus. Any ideas or work arounds?
Hmmm, I suspect your IT people have turned off sharing functionality in your version of Excel. I'd take it up with them.
Is is possible to use power query to get folder/library/file share permissions? If so, how?
You can get data from SharePoint Folders or Lists using the build in connectors available under the Get Data button on the Data tab. Libraries aren't listed, so not sure about that. A file can get selected from a folder.
I have a problem when I want to recall data from sharepoint again after I did not use it for a time. I use power query to pull data from sharepoint, but it always stuck at data do not allow to use, kind of my source were gone even though my first time there was nothing stuck. Could you please me find the solution about this?
Thank you in advance.
Not sure what would be causing that error.
@@MyOnlineTrainingHub It is kind of authorization of my organization blocks my assess every time. But anyway, your video help me a lot to import data from different data sources. Thank you for sharing this clip.
Hi Mynda, I am using Excel 2016. the Get Data sequence seems to be different and when connecting to SharePoint does not ask for your credentials to access SharePoint. Keep getting an error, "Proxy authentication required". How do we get past this point?
Hi Keith, the SharePoint Folder connector isn't available in Excel 2016. Some versions have the SharePoint List connector: docs.microsoft.com/en-us/power-query/connectors/sharepointlist
Hello Mynda (instructor of the decade :) I must be missing something, but I have a massive project/dashboard created using Power Query on my local pc that has many files and folders, I'm needing to move them all to one Drive for business; however, the techy side of me tells me that there must be an easier way than changing one folder path at a time. Hoping you can help :)
The method for getting data from OneDrive is different to From File. You can start a new query from OneDrive and then copy the code to the original queries.
Finally I found the video I wanted! Thanks for sharing this usefull content
Glad it was helpful!
Thanks for sharing this video, I was following your video and trying to select files form one drive, but I could not complete all the steps. While I clicked combine files button a popup of "evaluating the query" appears and stops. Please support on this.
Not sure what the problem might be, Amruth. Maybe try checking you have the correct credentials entered and also try rebooting.
You´re amazing with the explanation, in other hand microsoft are so burocratic that hurts
Thank you!
Great video. Thanks very much. But I've been looking for ways where I can retrieve data from Connection only queries rather than creating a connection with table, so when I share my file with external or 3rd party people, they would have access to the source files. Is there a way ? Please help.
I wonder if Power Query Custom Data types would be useful to you: www.myonlinetraininghub.com/power-query-custom-data-types
If you still have questions, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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!!!!!!!
So pleased I could help!
That's great tutorial and it's very helpful. Could you please share the link for SharePoint Connector? My office 365 doesn't come with it. Is it free? Thank you again!
Glad you found it helpful, Leo! I can't share the SharePoint connector. You either have a version of Excel that has it or you don't.
Excellent. Just what I need to do. But I can't get working. My Sharepoint site is quite large, and when I try to import it into the PQ I'm not getting a Table with one line per element. I either get nothing, or I get a relatively short table that doesn't include the path I want. Any ideas?
Hard to say, Andy 🤷♀️ I’d try variations of the path.
How do I retrive Data from SharePoint Folder if I use Excel 2013? The field "From Sharepoint Folder" does not exist.
Maybe you can sync your SharePoint files to your hard drive and access them using the From Folder connector instead.
@@MyOnlineTrainingHub I made it work! With the synchronisation to my hard drive I had the problem of colleagues not being able to refresh the data.
When I used the web connection it worked! I had to erade all the folders of the directory tough!
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 ?
It's not available for OneDrive personal.
Well this helped a lot! Thank YOU for creating this video!!! :)
You're so welcome!
Hi great video! Helped me however once i had gone through the steps and then tried to download it said 'download not complete' The reason being ' key not found' i don't know how to identify what is causing the error. Can you advise?
Hi Talib, Key Not Found errors are caused by Power Query expecting hard coded sheet/column names that differ to those in the file you're connecting to.
Thanks for the 3 ways to do this...hopefully MSFT makes it easier in the future 🤔
I hope so too, Doug!