How to Restore Organizational Account Authentication in Power Query
Question details
The user needs to restore the Organizational Account authentication option that has disappeared when importing a SharePoint file into Power Query.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Importing data from a SharePoint file into Power Query for data transformation.
- Observed behavior
- The Organizational Account authentication option is missing or fails to appear, likely due to stored or corrupted global data source permissions.
Ensure you have the correct SharePoint file URL and your organizational Microsoft 365 login credentials handy before clearing your existing data source permissions.
Clear Global Data Source Permissions in Power Query
Deleting corrupted or outdated global permissions forces Power Query to prompt for all authentication methods again, including the Organizational Account option.
Power Query remembers how you previously authenticated to different data sources. If a connection fails or was initially set up with the wrong method (such as Anonymous or Windows), it will hide the other options on subsequent attempts. Clearing the global permissions resets this behavior.
In Excel, navigate to the 'Data' tab on the ribbon. Click on 'Get Data' and select 'Data Source Settings' from the dropdown menu. If you are already in the Power Query Editor, you can find 'Data Source Settings' under the Home tab.
In the Data Source Settings dialog box that appears, select the 'Global permissions' radio button at the top to view all saved credentials across your workbooks.
Scroll through the list to find the SharePoint source URL you are trying to connect to. Select it, click the 'Clear Permissions' button at the bottom, and confirm the deletion.
Close the settings window and attempt to import your SharePoint file again. The standard authentication prompt will now appear with all five methods available. Select 'Organizational account' and click 'Sign in' to authenticate.
Looking for a Lightweight Alternative for Data Processing?
If you frequently encounter complex permission issues or heavy resource consumption in Microsoft Excel's Power Query, consider trying WPS Office. It provides a lightweight, highly compatible spreadsheet solution for your daily data analysis and formatting tasks without the hassle of intricate background connectors.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free installation package and follow the on-screen instructions to set it up on your computer.
- 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx or .csv data files directly with zero format loss.
- 3. Analyze and Process Data: Utilize WPS Spreadsheets' robust built-in data tools to filter, process, and analyze your datasets efficiently.
Frequently Asked Questions
Why did my Organizational Account option disappear in Power Query?
This usually happens when Power Query previously saved invalid, expired, or incorrect credentials (like Anonymous) for a specific SharePoint URL. By default, it bypasses the selection screen if it thinks it already has a saved method. Clearing the global permissions forces Power Query to ask you for the login method again.
Does clearing Data Source Settings delete my existing queries or data?
No. Clearing permissions in Data Source Settings only removes the saved login credentials used to connect to the source. Your actual queries, transformation steps, and data models remain completely intact.
How do I sign in with a different Organizational Account for the same SharePoint site?
Go to Data > Get Data > Data Source Settings > Global Permissions. Select your SharePoint site and click 'Edit Permissions'. Under the Credentials section, click 'Edit' to sign out of the current account and log in with your new organizational credentials.
