Power Query | Other users on my team cannot refresh data stored on Team SharePoint site

[HoopinIrishParent]

I have set up a SharePoint folder for my team that contains daily financial transaction reports.   Using Excel's Power Query functionality, I have developed a consolidation query that will go to this folder and bring back financial data into my excel report.  

I can update the data with no problem, however, when my colleague (with the same SharePoint permissions as me) attempts to refresh the data, they get the following error message:

Here are the Query Data Source Settings for the current workbook as well as the global permissions:

What else can I check to address this problem?

[#2 · Accepted solution]

@HoopinIrishParent 

The error message "We were unable to refresh one or more data connections in this workbook" indicates that your colleagues are encountering issues refreshing the Power Query data connection in your Excel workbook that retrieves data from the SharePoint folder.

Here are some additional checks you can perform to address this problem:

1. Check SharePoint permissions:

• Verify file-level permissions: Ensure that your colleagues have Read access to the specific Excel file containing the Power Query connection.
• Consider folder-level permissions: If the data source file resides within a SharePoint folder, confirm that your colleagues have sufficient permissions to access the folder itself. Inherited permissions from parent folders might not be enough.

2. Authentication method:

• Review the authentication method: In the Power Query Editor, go to Home > Manage Connections.
• Check the authentication method used for the connection to the SharePoint folder.
• If it's set to Windows authentication, ensure your colleagues have proper login credentials to access the SharePoint site.
• Consider switching to Anonymous authentication if appropriate for your scenario. This avoids individual credential requirements but might have security implications.

3. Data source format:

• File format compatibility: Verify that the data files in the SharePoint folder are in a format compatible with Power Query (e.g., CSV, Excel, TXT).
• Encoding issues: If using text files, ensure consistent encoding (e.g., UTF-8) across all files to avoid parsing errors.

4. Excel settings and add-ins:

• Check for conflicting add-ins: Temporarily disable any Excel add-ins that might interfere with Power Query functionality.
• Repair or reinstall Excel: If the issue persists, consider repairing or reinstalling Excel as a last resort.

5. Analyze the provided image:

The image you sent might contain additional clues about the error. Look for details like:

• Specific connection name failing to refresh.
• Any underlying error messages within the image that could provide more context.

By systematically checking these aspects, you can narrow down the potential causes and identify the root of the issue preventing your colleagues from refreshing the Power Query data connection.