How to Fix Power Query Cannot Load Data from a SharePoint Folder in Excel
Question details
The user is unable to load data from a SharePoint folder using Power Query due to incorrect URL formatting or credential issues.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Trying to connect to and import data from a SharePoint folder into Excel using the Power Query SharePoint Folder connector.
- Observed behavior
- Power Query fails to establish a connection or load the files, often caused by providing a deep folder link instead of the root site URL, or because of cached incorrect account credentials.
Ensure you have the exact root URL of your SharePoint site (ending at the site name, not the specific folder path) and verify that your Microsoft account has permission to view the files in that SharePoint site.
Clear Data Source Permissions and Reconnect Using the Site URL
Resetting your global permissions ensures that outdated credentials do not block access, and using the root site URL allows the SharePoint Folder connector to retrieve the file list properly.
The most common reason for this error is pasting the full URL of the specific SharePoint folder instead of the root site URL. Power Query requires the base site URL to initiate the connection. Additionally, clearing cached credentials prevents authentication conflicts.
Open Excel, go to the 'Data' tab, click 'Get Data', and select 'Data Source Settings'. Choose 'Global Permissions' at the top, and click 'Clear All Permissions' to remove any old cached credentials.
Navigate to 'Data' > 'Get Data' > 'From File' > 'From SharePoint Folder'.
In the dialog box, enter the full SharePoint site URL (e.g., https://domain.sharepoint.com/sites/SiteName). Do not include the specific document library or subfolder path at this stage.
Select 'Microsoft account' from the authentication options on the left side of the prompt, click 'Sign In', enter your Microsoft credentials, and then click 'Connect'.
Once the connection is established and the Power Query Editor opens, locate the 'Folder Path' column. Click the filter dropdown on this column to isolate and display only the files from your required SharePoint folder.
Try WPS Office for Your Data Analysis and Spreadsheet Needs
While Power Query's SharePoint connection is specific to the Microsoft ecosystem, WPS Office provides a lightweight, highly compatible, and free alternative for your everyday spreadsheet processing. It offers a familiar interface, seamless migration, and robust data tools without the heavy subscription costs.
Frequently Asked Questions
Why does Power Query say the SharePoint folder path is invalid?
This happens if you enter the full path to a specific folder or document library. The SharePoint Folder connector requires the root site URL (e.g., https://domain.sharepoint.com/sites/SiteName). You must connect to the site first and then filter down to your target folder within the Power Query Editor.
How do I fix authentication errors when connecting to SharePoint in Excel?
Authentication errors usually occur due to cached, expired, or incorrect credentials. You can resolve this by navigating to Data > Get Data > Data Source Settings > Global Permissions, and clicking 'Clear All Permissions' before trying to connect again and signing in with the correct Microsoft account.
Can I connect to a specific SharePoint folder directly without loading all site files?
Standard Power Query requires connecting to the site level first when using the 'SharePoint Folder' connector. However, to optimize performance, you should filter the 'Folder Path' column immediately in the Power Query Editor before expanding the file binaries, preventing unnecessary data from loading.
