How to Fix Excel Power Query Prompting for Sign-In on Every SharePoint Query
Question details
Excel Power Query repeatedly prompts the user to sign in for each individual query connected to SharePoint Online.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Retrieving data from multiple Excel files hosted on SharePoint Online using Power Query.
- Observed behavior
- The system prompts for separate authentication for every query after the MFA trusted period expires, even though the on-premises domain is synced with Microsoft Entra ID.
Ensure you have administrative access to your Microsoft Entra Connect configuration and verify that your organizational Active Directory is properly synced with Microsoft Entra ID.
Enable Microsoft Entra Seamless Single Sign-On (SSO)
Configuring Seamless SSO ensures users are automatically authenticated across all SharePoint queries without repeated credential prompts.
By enabling Microsoft Entra Seamless SSO through Azure AD Connect, the authentication token is passed silently across Microsoft 365 services. This eliminates the need for Power Query to prompt for multi-factor authentication on every individual SharePoint file retrieval.
Open the Microsoft Entra Connect (formerly Azure AD Connect) tool on your organization's domain server.
Click on 'Change user sign-in' and proceed to the sign-in method configuration page.
Check the box labeled 'Enable single sign-on'. You will be prompted to enter your domain administrator credentials to authorize the change.
Finish the wizard to apply the Seamless SSO settings to your tenant. Users will need to restart their Office applications for the changes to take effect.
Clear Stale Data Source Permissions in Excel
Clearing old or conflicting credentials forces Excel to establish a fresh, unified authentication token for SharePoint.
Experience a Lighter, Faster Alternative to Microsoft Excel
If enterprise configuration issues and constant credential prompts disrupt your workflow, consider WPS Office. It provides a highly compatible, lightweight, and free spreadsheet solution without complex authentication loops.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install WPS Office: Run the downloaded setup file and follow the quick on-screen instructions to install the suite.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files directly. You can begin editing and analyzing data immediately.
Frequently Asked Questions
Why does Power Query ask for credentials even if my account is synced?
This usually happens when Seamless Single Sign-On (SSO) is not fully enabled in Microsoft Entra Connect, or if stale credentials stored in Excel's Data Source Settings conflict with your current multi-factor authentication (MFA) token.
How do I clear my Power Query cache in Excel?
Go to the Data tab > Get Data > Query Options. Under the Global section on the left pane, select 'Data Load', and then click the 'Clear Cache' button. This helps resolve authentication loops caused by corrupted cache data.
Can I use an API key instead of an organizational account for SharePoint in Power Query?
No, SharePoint Online data sources in Power Query natively require an Organizational Account or Windows authentication. API keys are generally used for external third-party web APIs, not internal Microsoft 365 services.
