Nowadays many of us are saving our files to the cloud using OneDrive for Business or SharePoint Online. Unfortunately, it’s not straight forward to get data form OneDrive or SharePoint with Power Query, so in this tutorial I’m going to step you through the three scenarios: getting data from an individual file on OneDrive or SharePoint, getting data from a SharePoint folder, and getting data from a SharePoint shared library.
Note: The SharePoint Folder connector is only available in Excel 2019 (or later) Professional Plus and with a Microsoft 365 Apps for Enterprise license.
Table of Contents
1. Get Data from a Single File on OneDrive or SharePoint with Power Query2. Get Data from a Folder on OneDrive or SharePoint with Power Query
3. Get Data from a Shared Library on SharePoint with Power Query
4. Sharing Files with Power Query Connections to OneDrive or SharePoint
5. More Power Query
Watch the Video
Get Data from a Single File on OneDrive or SharePoint with Power Query
I’ll start with getting data from an individual file on OneDrive or SharePoint. Either way the process is the same and it requires getting the file path, but not the file path as you know it.
If you’re like me, you’ll have your OneDrive files synced to your hard drive. You can see mine here in the file explorer:
If you try to copy a file’s path from here it will reference the copy of the file on your hard drive, not the OneDrive/SharePoint file path.
The trick is to go to OneDrive or SharePoint online and open the file from there in the Desktop App:
Then in Excel go to the File tab > Info and click the ‘Copy Path’ button:
Now that you have the file path you can close the file and create a query in a new Excel workbook using the From Web connector in Power BI or Excel (shown below):
Paste the URL for the file into the dialog box, deleting the ?web=1 from the end before pressing OK:
At the authentication dialog box choose Organizational Account and enter your OneDrive or SharePoint logon credentials:
At the navigator dialog box, you can choose the sheet(s) or table(s) from the list and then click Transform Data to load the data to the Query Editor where you can perform further transformations before loading to Excel or the Data Model:
Get Data from a Folder on OneDrive or SharePoint with Power Query
Getting data from a folder on OneDrive or SharePoint requires a different approach. First, we need the folder path, which you get from your browser. In the image below I want to get data from the folder called pq_2.05_excel_workbooks:
I simply copy the portion of the URL up to _layouts/15…, which you can see highlighted in pink.
Then in Power BI or Excel (shown below) you want the From SharePoint Folder connector:
Paste in the URL and at the authentication dialog box choose Microsoft Account and sign in with your SharePoint logon credentials:
Next, you’ll see a list of the files on your SharePoint site. Click Transform data to filter the list to the folder you want:
Filter the Folder Path column to only show files that are contained in the folder you want. You can use Filter > Text Contains > to enter part of your folder path string. e.g. my files are in a folder with this path:
Training/Training Content/ Syllabuses MOTH/Excel/Power Query/Lessons/Practice Files/pq_2.05_excel_workbooks:
IMPORTANT: remember Power Query is case sensitive, so you must enter the folder path exactly as it appears in SharePoint.
In the Power Query Editor, click the double down arrow on the Content column to get the data from the files in the folder:
Choose the Table/Sheet that you want from each file, then click OK:
IMPORTANT: the sheet or table that you choose must have the same name in each file you want to consolidate.
This will consolidate the data in each file into one table and from there you can perform further transformations.
Get Data from a Shared Library on SharePoint with Power Query
Lastly, if you work with Shared Libraries in SharePoint Online then the process is a hybrid of the first and second examples. You can see in the screenshot below that I’m in our My Online Training Hub Team Site and I want to get the data from the Example Data folder. You can see it’s a Shared Library from the list on the left.
There are two ways you can get the URL for the shared library, one is to copy the domain from the URL, but you need to remove the -my from the URL before pasting it into Power Query.
Alternatively, if you open one of the files in the folder in the desktop app…
And copy the path from the File tab > Info
You’ll notice the URL you get through this path does not include ‘-my’ so it’s ready to use by selecting the path up to and including .sharepoint.com/ e.g.:
https://your_tenant.sharepoint.com/
Then in Power BI or Excel (shown below) you want the From SharePoint Folder connector:
And the process from here is the same as the previous example.
Sharing Files with Power Query Connections to OneDrive or SharePoint
If you want to share files with other users you need to make sure they have permission to access the file, folder or shared library.
You can do this by right-clicking the file, folder or library in OneDrive or SharePoint > Share:
Then in Power Query they’ll also need to enter their credentials. Power Query will prompt them to edit their credentials when they open the query editor. As shown below where you can see a yellow warning bar below the ribbon. Alternatively they can edit them via the Data Source Settings on the Home tab.
- Select the source from the list
- Edit Permissions
- Edit to switch to a different account
Thanks
Big thanks to fellow Microsoft MVP, Wyn Hopkins for demystifying connecting to OneDrive or SharePoint in his video here.
Learn Power Query
Power Query not only transforms data, it can transform your work load with significant efficiency gains. Check out this introduction to Power Query video. And if you're ready for more, sign up for my Power Query course.
Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI
Brandon Harrison
Hi,
After getting my Office version upgraded, I am unable to get the SharePoint connector to see any folders (https://companypl.sharepoint.com/). I can get it to find personal OneDrive / Sharepoint folders, but not those on the company SharePoint site I created and am owner. Other Excel files created with Power Query references to specific files are in the same location and can be used by others.
Are there specific rights that must be allocated in SharePoint to allow shared folders to work in PQ?
Thanks
Mynda Treacy
I wonder if you’re logged into Excel with the wrong account. Try logging in with your company 365 account that matches the company SharePoint.
Brandon Harrison
Hi Mynda,
I’m logged in with the correct credentials. If I put the SharePoint root URL for the company SharePoint site, then the “Folder Path” shows all null values.
This is a Teams SharePoint folder. Will that make any difference?
Interestingly, if I use the “From Web” process to connect to a single file in the folder that I want access to for all the files, then it works fine. Its just not allowing me to connect using the SharePoint Folder Connector.
Any ideas on why?
Mynda Treacy
Interesting, Brandon. Maybe it has something to do with the authentication process. When you connect using ‘From Web’ are you seeing all the folders, or are you only able to connect to a single file?
Brandon Harrison
Hi Mynda,
I see all files in the folder.
Mynda Treacy
Thanks for confirming. I suspect something has changed since I wrote this post. I’ll have to test at my end to see if I get the same results.
pgSystemTester
Unbelievably how unintuitive Microsoft makes this process. Thanks for simplifying.
Mynda Treacy
Glad we can help
Leslie
Thanks for your video, I have tried this, when I try to apply the filter and find the documents I need i dont have any luck, keeps showing error, can this be because there are too many documents saved at this sare?
How can I find my documents to finish the query
Thanks
Mynda Treacy
Hard to say without seeing the files etc. Try troubleshooting with a smaller dataset to see if that’s the problem. Create a test folder with two files.
Tamer
Hi,
have a good day
After saving a file in OneDrive to the local computer with the macro code;
When I want to open the recorded file, as in the attached image:
I get the error message “The file format or extension is not valid……….”
How can we save a file in the link below to the local computer?
Link: https://onedrive.live.com/view.aspx?
resid=1376F399A4120875!1937&ithint=file%2cxlsx&authkey=!Ag3bU1cS7sd7Xko
thanks in advance
Best
Mynda Treacy
Hi Tamer, If you want to save your OneDrive files on your local drive, you should use the OneDrive sync tool.
lookrider
Hi Mynda,
“Note: The SharePoint Folder connector is only available in Excel 2019 Professional Plus and with a Microsoft 365 Apps for Enterprise license.”
I guess this means that if I have a normal Microsoft 365 licence, I can’t use this function?
I have also seen that when I copy the OneDrive link, it points to a “https://d.docs.live.net …” address
Mynda Treacy
Not unless you have the Apps for Enterprise 365 license. I’d check your version though just in case something has changed.
Brian
How do I get a AccessDB link if there is no way to Open an Access file in App option? The Access files only allow download to desktop which isn’t the SP moth.my link.
Mynda Treacy
Hi Brian, have you tried using the Get Data > From Web connector?
H S Kalsi
i tried all the suggested methods but i am unable to get the excel file from SharePoint to Power BI.
Lukasz
Hi Mynda,
I noticed quite strange behaviour loading data from SharePoint. I’ve recreated my local folders the same way I have them on my local drive. However, when I use connection to Sharepoint Excel doesn’t pull all of the data. I’ve noticed it on 20k lines Excel files. So, I have 3 very similar files. On my local version when I combine and remove duplicates I get 25,021 lines. However, using the same technique with Sharepoint folder 3,715 lines.
I guess that loading the connect from Share point it limits it to 2,000 lines. So, what I get is only a part of new data. Do you know how to get all of the data when loading from Sharepoint? The alternative option would be to split the files under the limit and then upload them to Sharepoint.
Kind regards,
Lukasz
Mynda Treacy
I’m not aware of any limit loading data from SharePoint. It should get all of the data. Maybe there’s a delay in syncing the data from your local drive to the file on SharePoint?
Roshan Matooreah
Hi , I am Roshan from Mauritius , i wanted to know , how do i publish or share a powery query report but only the report and it should be dynamic , when my manager opens it
Mynda Treacy
Hi Roshan,
Power Query doesn’t return reports as such. Power Query returns a dataset from which you can create a report. For more on automatic refresh, see this tutorial: https://www.myonlinetraininghub.com/auto-refresh-pivottables
Mynda
Beth
I have a SP library with several folders and custom columns. I’m attempting to get the files and custom columns from this library using your approach in Get Data from a Folder on OneDrive or SharePoint with Power Query section. I chose transform data and add a filter query on Folder Path. My query is = Table.SelectRows(Source, each Text.Contains([Folder Path], “CODQ Archive”)). The top row points to a Word .docx file. When I click the Content double-down arrows, it evaluates for several minutes and then I receive a message: Combine Files We didn’t recognize the format of your first file (mycompanyname.sharepoint.com). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again.
So I removed that Word doc. The rest of the files are mostly PDF. Again I click Content double-down arrows. Power Query evaluates for awhile. Then I received a message: Connecting. Please wait while we establish a connection to PDF. After that’s complete, the Combine Files screen shows Display Options > Parameter1 [4] Page (001-004). There is no data in them when I select the pages. Table and sheet are not even listed under this.
I’m not sure if I’m doing something wrong or if what I’ve trying to achieve is possible. Please adivse. Thank you.
Mynda Treacy
Hi Beth,
Try saving the PDF file to your desktop and use the Get Data > From File > PDF connector to rule out that it’s not the file that’s the problem.
Mynda
Samuel
Great post, Mynda. Thank you.
Does this work for OneDrive Personal?
Mynda Treacy
Hi Samuel, no, as per the first sentence, it’s only available with OneDrive for Business, sorry.
Chris
I found this post very helpful after trying PowerQuery for the first time. The worksheet and Powerquery were to be used by my team and the files were kept on a Sharepoint folder. Everything worked fine when I built the sheet but it failed when others tried using it. So the solution you’ve detailed (for SharePoint shared folders) was just what the doctor ordered. However, when I trying \Data\Get Data\From File I couldn’t find the SharePoint options. I think (?) this is a licensing issue; the Microsoft 365 Business Premium license we use doesn’t have this feature 🙁
Mynda Treacy
Hi Chris,
Yes, unfortunately this is a licensing limitation 🙁 The SharePoint connectors are available in 365 Apps for Enterprise or the Office 2019 standalone licenses.
Mynda