• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Get Data from OneDrive or SharePoint with Power Query

You are here: Home / Power Query / Get Data from OneDrive or SharePoint with Power Query
get data from sharepoint or OneDrive
March 18, 2021 by Mynda Treacy

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 Professional Plus and with a Microsoft 365 Apps for Enterprise license.

Watch the Video

Subscribe YouTube

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:

OneDrive or SharePoint files syncd to hard drive

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:

get data from OneDrive or SharePoint with Power Query

Then in Excel go to the File tab > Info and click the ‘Copy Path’ button:

copy path for OneDrive or SharePoint file for Power Query

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

Excel Power Query get data from web

Paste the URL for the file into the dialog box, deleting the ?web=1 from the end before pressing OK:

enter URL for Power Query to get data from OneDrive or SharePoint

At the authentication dialog box choose Organizational Account and enter your OneDrive or SharePoint logon credentials:

enter credentials for Power Query to get data from OneDrive or SharePoint

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:

Power Query navigator to get data from OneDrive or SharePoint

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:

getting SharePoint folder path for Power Query

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:

Get Data from OneDrive or SharePoint with Power Query

Paste in the URL and at the authentication dialog box choose Microsoft Account and sign in with your SharePoint logon credentials:

enter 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:

Transform OneDrive or SharePoint data with Power Query

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:

Filter OneDrive or SharePoint folders with Power Query

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:

Combine Data from OneDrive or SharePoint folder with Power Query

Choose the Table/Sheet that you want from each file, then click OK:

choose table to combine

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.

Get Data from SharePoint shared libraries with Power Query

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.

 URL to get data from SharePoint Shared Libraries with 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

Copy path from SharePoint file

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:

Get Data from SharePoint folder menu

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:

Share files on OneDrive or SharePoint

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.

Edit credentials in Power Query

  1. Select the source from the list
  2. Edit Permissions
  3. Edit to switch to a different account

Enter new credentials in Power Query

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.

get data from sharepoint or OneDrive

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI
Next Post:Change Type Using Locale with Power QueryPower Query change type using locale

Reader Interactions

Comments

  1. Leslie

    August 25, 2022 at 12:53 am

    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

    Reply
    • Mynda Treacy

      August 25, 2022 at 8:43 am

      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.

      Reply
  2. Tamer

    July 4, 2022 at 12:29 am

    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

    Reply
    • Mynda Treacy

      July 4, 2022 at 9:20 am

      Hi Tamer, If you want to save your OneDrive files on your local drive, you should use the OneDrive sync tool.

      Reply
  3. lookrider

    April 27, 2022 at 7:25 pm

    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

    Reply
    • Mynda Treacy

      April 27, 2022 at 7:30 pm

      Not unless you have the Apps for Enterprise 365 license. I’d check your version though just in case something has changed.

      Reply
  4. Brian

    April 13, 2022 at 4:05 am

    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.

    Reply
    • Mynda Treacy

      April 13, 2022 at 9:37 am

      Hi Brian, have you tried using the Get Data > From Web connector?

      Reply
  5. H S Kalsi

    March 9, 2022 at 1:19 pm

    i tried all the suggested methods but i am unable to get the excel file from SharePoint to Power BI.

    Reply
  6. Lukasz

    November 22, 2021 at 10:02 pm

    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

    Reply
    • Mynda Treacy

      November 23, 2021 at 11:34 am

      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?

      Reply
  7. Roshan Matooreah

    July 30, 2021 at 12:26 am

    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

    Reply
    • Mynda Treacy

      July 30, 2021 at 8:47 pm

      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

      Reply
  8. Beth

    June 30, 2021 at 1:14 am

    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.

    Reply
    • Mynda Treacy

      June 30, 2021 at 9:46 am

      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

      Reply
  9. Samuel

    June 26, 2021 at 3:34 pm

    Great post, Mynda. Thank you.

    Does this work for OneDrive Personal?

    Reply
    • Mynda Treacy

      June 26, 2021 at 3:43 pm

      Hi Samuel, no, as per the first sentence, it’s only available with OneDrive for Business, sorry.

      Reply
  10. Chris

    May 17, 2021 at 6:24 am

    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 🙁

    Reply
    • Mynda Treacy

      May 17, 2021 at 8:48 am

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.