Troubleshooting SharePoint Folder Connection Failures in Power Query

A SharePoint folder connection failure in Power Query occurs when Excel cannot establish an authenticated link to a SharePoint Online site, rejects the provided URL structure, or fails to parse the document library schema. This typically generates errors like Access to the resource is forbidden, We couldn't authenticate with the credentials provided, or leaves the query stuck on an endless schema-evaluation loop.

Fast Fix

Ensure the URL entered into the connector is truncated strictly to the SharePoint site collection root: https://<tenant>.sharepoint.com/sites/<SiteName>/. Remove document library names, subfolders, or view strings (such as AllItems.aspx). Under authentication, select Organizational Account, click Sign in, sign into your Microsoft 365 profile, and click Connect.

---

Diagnostic Snapshot

Error / Symptom	Most Likely Cause	Immediate Fix
“Access to the resource is forbidden”	Connecting to library subpath instead of site root, or using Windows Auth instead of M365.	Truncate URL to root site and set auth to Organizational Account.
“We couldn’t authenticate…”	Stale OAuth token in Windows Credential Manager or Excel cache.	Clear domain entry under Data Source Settings.
Evaluation Hangs / Out of Memory	Using SharePoint.Files on a site collection containing tens of thousands of files.	Switch connector implementation to SharePoint.Contents.
“The user was not authenticated”	Guest or multi-tenant account mismatch.	Clear permissions and sign in using the exact home tenant account.

---

Common Setup Errors & Corrections

1. Using a Deep-Folder URL Instead of the Site Collection Root

The most common mistake when using the SharePoint Folder connector is pasting the URL straight from the browser navigation bar while viewing the folder.

• Incorrect URL: https://company.sharepoint.com/sites/Finance/Shared%20Documents/MonthlyReports/2026
• Correct URL: https://company.sharepoint.com/sites/Finance

The Power Query SharePoint connector is built to evaluate the entire site root and return a list of all files in the library, which you then filter down inside the query editor. Passing a specific subfolder path into the initial dialog causes the SharePoint REST API to reject the connection.

Browser URL:  <https://tenant.sharepoint.com/sites/TeamName/Shared> Documents/Subfolder
                                           └──────┬──────┘
Pasted to PQ: <https://tenant.sharepoint.com/sites/TeamName>

---

2. Selecting the Wrong Authentication Provider

When the connector prompt appears, Power Query presents multiple authentication methods:

• Anonymous
• Windows
• Basic
• Organizational Account

For SharePoint Online (Microsoft 365), you must select Organizational Account.

• Using Windows attempts to send NTLM/Kerberos tokens, which SharePoint Online rejects.
• Using Basic attempts to transmit cleartext credentials, which are blocked by Microsoft 365 tenant security baselines.

If you previously clicked the wrong option, Excel saves that invalid selection permanently until cleared manually.

[Access Web content]
  ├── Anonymous              (Fails on M365)
  ├── Windows                (Fails on Cloud)
  ├── Basic                  (Blocked by Entra ID)
  └── Organizational account (REQUIRED) -> [Sign In]

---

Step-by-Step Troubleshooting Procedure

Step 1: Purge Corrupted or Stale Cached Credentials

If permissions were changed or you switched Microsoft accounts, Excel’s stored authentication tokens may be invalid.

1. In Excel, navigate to Data > Get Data > Data Source Settings.
2. Select Global permissions.
3. Find all entries referencing your SharePoint tenant URL (https://yourcompany.sharepoint.com).
4. Click Clear Permissions for each entry.
5. Close the dialog.

If clearing credentials inside Excel does not force a re-login prompt, stale identity tokens may be lingering in the OS. Follow the complete credential wipe procedure in How to Clear Office Credential Cache on Windows 11 (The Modern Way).

---

Step 2: Establish the Connection Correctly

1. In Excel, go to Data > Get Data > From File > From SharePoint Folder.
2. Enter the clean site URL: https://<tenant>.sharepoint.com/sites/<SiteName>.
3. In the authentication screen, select Organizational account in the left column.
4. Click Sign in. Complete the browser authentication prompt (including MFA).
5. Once marked as “You are currently signed in”, click Connect.

---

Step 3: Switch from SharePoint.Files to SharePoint.Contents for Large Libraries

By default, the SharePoint Folder connector calls the M function SharePoint.Files. This function generates metadata for every single item across the entire document library collection before you can apply a filter. If your SharePoint site hosts thousands of files, this causes the query to hang, timeout, or crash with memory errors.

To connect directly to large repositories, switch to SharePoint.Contents, which allows folder-by-folder navigation similar to File Explorer.

1. Launch Power Query Editor with your failing query.
2. In the Home tab, click Advanced Editor.
3. In the formula text, locate SharePoint.Files: Source = SharePoint.Files("<https://company.sharepoint.com/sites/Finance>", [ApiVersion = 15])
4. Replace SharePoint.Files with SharePoint.Contents: Source = SharePoint.Contents("<https://company.sharepoint.com/sites/Finance>", [ApiVersion = 15])
5. Click Done.
6. Power Query will now display a table of document libraries. Click the Table link in the Content column to step down through your specific folders until you reach your target files.

---

When to Escalate to SharePoint / M365 Administrators

If the URL is verified, credentials have been cleared, and the sign-in succeeds but you receive an immediate 403 Forbidden error upon hitting Connect, the block is on the tenant side.

Check the following with your administrator:

• Entra ID Conditional Access Policies: Policies requiring compliant devices or blocking non-browser clients can allow you to view the SharePoint folder in Microsoft Edge while blocking Excel’s background mashup engine.
• Limited Access / Site Permissions: If you were granted access to a specific file or subfolder rather than the parent document library, the SharePoint Folder connector cannot query the site root. The administrator must grant at least Read permissions at the library level.

For deeper troubleshooting on permission inheritance issues within Microsoft 365, refer to “Access Denied” to Teams Files despite being Team Member. If the connection still returns raw HTTP 403 access issues, consult How to Resolve Power Query “Access to the resource is forbidden” (403 Error).