Power Query Refresh Fails with SharePoint 500 Internal Server Error

Hi @harish1199

Thank you for sharing your concerns. 

Based on my research, looks like the error message “(500) Internal Server Error” usually indicates that the request sent by Power Query to SharePoint is failing on the server side, rather than being caused by an issue with your query configuration or Excel file. Since the same query works for another user in your organization, this strongly suggests a user-specific issue rather than a problem with the data source itself. Power Query maintains its own stored credentials and connection context, which can vary between users even when they are accessing the same SharePoint file. 

You may refer to the solution provided by Q&A Assist and follow these steps below: 

Reconnect Using the Proper SharePoint Connector  

Go to Data > Get Data > From File > From SharePoint Folder. 

Paste only the site URL (for example, your SharePoint site link, not the full file path). 

Then, sign in using your Organizational account. After connecting: Go to Data > Queries & Connections > Right-click your query > Edit.

Navigate to Home > Data Source Settings.

Clear Permissions for all SharePoint entries again. 

Refresh the query and try again to see if the issue still persists. 

Check and clean the source path 

Ensure the Source step uses a clean SharePoint URL.  

Avoid URLs containing parameters such as ?web=1. 

Ensure the query is not referencing any local path. 

Additionally, you could try closing Excel completely and reopening it. Then, test with a new blank workbook and recreate the query from scratch or compare your setup with a working user’s version to identify any differences. 

Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution. 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

The error indicates a failure when Excel (Power Query) calls the remote SharePoint service during refresh. A 500 Internal Server Error is a server-side failure, but from the Excel/Power Query side there are several common causes and checks:

1. Check the data source path and availability
   Even if the file can be opened in the browser, the path used by Power Query must still be valid and accessible:
   • In Excel, go to Data > Get Data > Data Source Settings.
   • Select the SharePoint connection and verify the URL and file path are correct and still exist.
   • If the file was moved, renamed, or the library structure changed, update the path using Change File Path and then select Get Data again.
2. Verify credentials and permissions
   Power Query uses its own stored credentials, which can differ per user:
   • In Data Source Settings, select the SharePoint connection and choose Clear Permissions.
   • Reconnect and provide the appropriate authentication method and credentials when prompted.
   • Ensure the account used has at least read access to the SharePoint site, library, and file. A subtle permission difference between users can cause one user to succeed and another to fail.
3. Handle data source errors systematically
   Power Query distinguishes between local and remote errors. A 500 error is a remote error from the SharePoint service:
   • Retry the refresh later in case of intermittent service or network issues.
   • Confirm with the SharePoint administrator whether there are changes to the site, maintenance, or throttling that might affect requests.
   • Check if there were changes to authentication (e.g., conditional access, MFA, or privacy settings) that might block the request for one user.
4. Check for locked or open files
   If the target Excel file on SharePoint is open or locked (for example, by another user or in a non-subscription version of Excel), the refresh may fail:
   • Ensure the source file is saved and closed.
   • If someone else has it open, ask them to close it or check it in.
5. Compare configuration with the working user
   Since another user can refresh successfully:
   • Compare the Data Source Settings between both users (URL, authentication method, privacy level).
   • Ensure both are using the same version of Excel and Power Query features.

If the issue persists after these checks, the next step is to involve the SharePoint administrator to review server logs for the 500 error and confirm there are no server-side issues or restrictions affecting this specific user or request pattern.

References:

• Import and shape data in Excel for Mac (Power Query)
• Handling data source errors (Power Query)