Fixing “The user was not authorized” when connecting to SharePoint lists.

The “The user was not authorized” error occurs when Power Query tries to connect to a SharePoint Online or SharePoint Server list using invalid, expired, or improperly scoped credentials. This error typically happens when Excel defaults to Windows authentication instead of an Organizational Account, or when the connection URL points to a specific list path rather than the root site.

Quick Answer:

Go to Data > Get Data > Data Source Settings. Select your SharePoint URL, click Clear Permissions, and click Close. Reconnect to your list. When the credential prompt appears, select Organizational Account in the left sidebar, click Sign In to authenticate using your Microsoft 365 account, and click Connect.

---

Why the Authorization Failure Happens

Power Query handles SharePoint differently from standard network shares. When connecting to SharePoint lists, authentication must validate through Microsoft Entra ID (formerly Azure AD) or the local SharePoint security token service.

The three most common causes of this failure are:

1. Wrong Credential Category: Excel attempts to use current Windows domain credentials instead of an authenticated Microsoft 365 Organizational Account.
2. Incorrect URL Scoping: The user enters the full browser URL of the list view (e.g., https://company.sharepoint.com/sites/Team/Lists/Tasks/AllItems.aspx) instead of the clean site root URL.
3. Stale Token Cache: Power Query stores an expired authentication token in its local data source registry and repeatedly sends the invalid token instead of requesting a new sign-in.

---

Step 1: Clear the Stale SharePoint Permissions

Before attempting to reconnect, clear any invalid permissions stored in Excel’s cache. Otherwise, Power Query will bypass the login prompt and reuse the failing token.

1. Open Excel and navigate to the Data tab.
2. Click Get Data > Data Source Settings.
3. In the dialog, select Global permissions to inspect all saved connections.
4. Scroll through the list and locate every entry referencing your SharePoint domain (e.g., https://yourtenant.sharepoint.com).
5. Select the entries and click Clear Permissions.
6. Click Delete to confirm, then click Close.

---

Step 2: Connect Using the Site Root URL

Power Query requires the URL of the SharePoint Site, not the direct link to the list or library. Entering list-level URLs breaks the authentication handshake.

Example of an Incorrect URL:

<https://mycompany.sharepoint.com/sites/Finance/Lists/Invoices/AllItems.aspx>

Example of the Correct Site URL:

<https://mycompany.sharepoint.com/sites/Finance>

Connecting:

1. Go to Data > Get Data > From Other Sources > From SharePoint List.
2. Paste only the base site URL into the prompt.
3. Select Implementation 2.0 (recommended for improved performance and accurate column type mapping) or 1.0, then click OK.

---

Step 3: Authenticate with an Organizational Account

When the connection dialog opens, selecting the wrong authentication method triggers the “user was not authorized” error immediately.

Credential Options Dialog:
├── [Anonymous]              ──► Fails on secured SharePoint sites
├── [Windows]                ──► Fails for SharePoint Online (Office 365)
├── [Basic]                  ──► Deprecated; blocked by modern tenants
└── [Organizational Account] ──► REQUIRED for SharePoint Online

1. In the left-hand panel of the credential prompt, click Organizational Account.
   (Note: If you are connecting to an older on-premises SharePoint Server inside an internal domain, review “Please specify how to connect”: Handling Anonymous vs. Windows Auth).
2. Click the Sign in button.
3. Complete the Microsoft 365 authentication flow, including multi-factor authentication (MFA) if prompted.
4. Once authenticated, the dialog will show: “You are currently signed in.”
5. Under Select which level to apply these settings to, choose the site level matching your project needs:
   • Select https://mycompany.sharepoint.com/sites/Finance to limit permissions to that department.
   • Select https://mycompany.sharepoint.com if you connect to lists across multiple sites in the tenant.
6. Click Connect.

If MFA or SSO redirect issues prevent your account from signing in successfully, refer to Power Query and OAuth2: Fixing “The credentials provided for the source are invalid.”

---

Step 4: Verify SharePoint User Access Rights

If you signed in with your Organizational Account but the authorization error persists, verify that your account has sufficient access in SharePoint:

1. Open your web browser and navigate to the SharePoint list.
2. Ensure you can view the items in the list using the same Microsoft 365 account.
3. Click the Settings gear in the upper-right corner of SharePoint and select Site permissions.
4. Your account must have at least Read permissions on the site collection. If your account only has access to a single list through direct item sharing, Power Query’s site-level API calls will be rejected with an authorization failure.

If SharePoint displays access-denied or checkout conflicts while loading data, check Troubleshooting “SharePoint: The file is currently checked out by another user.”

---

Next Steps for Automated Cloud Refreshes

If your query loads locally in Excel desktop but throws authorization errors when published to Excel Online or scheduled in the cloud, the underlying credentials must be configured in the web service. For guidance on configuring cloud-based data connections, see Handling “External Data Refresh Failed” on Excel Online / SharePoint.