Power Query Problems

Fix Power Query Refreshes Only When SharePoint Excel File Is Open

Huma Ashraf Ch Sep 28, 2026 869 views

Question details

The user needs to resolve an issue where a Power Query connection to a SharePoint Excel file fails to refresh unless the target file is actively open.

How to Fix Power Query Refreshes Only When SharePoint Excel File Is Open

Product: Microsoft Excel, Power Query, SharePoint
Device & OS: not provided
Scenario: Using Excel.Workbook(Web.Contents()) in Power Query to pull data from an Excel workbook hosted on SharePoint.
Observed behavior: The data refresh fails when the SharePoint Excel file is closed. It may be caused by the file being incomplete, corrupted, inaccessible, or actively being uploaded by Power Automate.

Before you start

Verify that you have the correct SharePoint file URL and that no automated workflows (like Power Automate) are currently running and locking the file.

Solution 1Recommended

Verify File Integrity and Upload Timing

Ensure the target Excel file is fully uploaded and not corrupted by testing it independently and adjusting automated workflows.

Power Query cannot refresh data from a file that is incomplete or currently being modified by an automated process like Power Automate. If the file is locked in an uploading state, the refresh will fail.

Test the file independently

Open a blank Excel workbook. Go to the Data tab, click 'Get Data' > 'From Other Sources' > 'From Web', and paste the SharePoint file URL to see if it loads successfully on its own.

Check Power Automate timing

If you are using Power Automate to create or update the file, open your flow and insert a 'Delay' action (e.g., 1-2 minutes) after the file creation step to ensure the file is completely uploaded before triggering any data refresh.

Validate file format

Ensure the file extension is a valid Excel format (like .xlsx). Open the SharePoint document library, click the file to open it in Excel Online, and verify that it opens without corruption warnings.

File Locks: If another user is actively saving the file during the exact moment your scheduled refresh triggers, the file may temporarily lock, causing a failure.

Solution 2

Check SharePoint Permissions and File Accessibility

Confirm that the account attempting to refresh the data has adequate permissions to read the specific SharePoint file without needing to open it.

Solution 3

Contact Microsoft 365 Support

Escalate the issue to Microsoft Support if the file is fully accessible and uploaded, but the Web.Contents() refresh still fails.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If you are tired of complex connection errors, permission conflicts, and SharePoint sync issues in Microsoft Office, try WPS Office. It provides a lightweight, highly compatible, and free alternative for managing your spreadsheets and daily tasks with ease.

1. Download and install: Visit the official WPS Office website to download the free installer and complete the setup process in minutes.
2. Open your Excel files: Launch WPS Spreadsheets and easily open your existing .xlsx files directly from your local drive or cloud storage.
3. Manage data efficiently: Use built-in data analysis tools, pivot tables, and advanced formulas without worrying about complex background refresh errors.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsLightweight installation and incredibly fast loading timesFree to use with a familiar, easy-to-learn user interfaceBuilt-in cloud storage for easy file sharing and collaboration

Frequently Asked Questions

Why does my Power Query say the SharePoint file is in use?

This usually happens if another user is actively modifying the file, or an automated background process like Power Automate is currently uploading or editing the file, locking it from being read by Power Query.

How can I delay my Power Query refresh until Power Automate finishes?

You can add a 'Delay' action in your Power Automate flow immediately after the file creation or update step. Setting a delay of a few minutes ensures the file is fully processed and unlocked before the query refresh runs.

Does Power Query require special permissions to read SharePoint files?

Yes. The account executing the Power Query refresh must have at least 'Read' permissions for both the specific file and the SharePoint site or folder where it is stored. Background refreshes will fail if the token expires or lacks these permissions.

Can I use local file paths instead of Web.Contents for SharePoint files?

If you have the SharePoint library synced to your computer via OneDrive, you can connect to the local synced file path. However, this relies on your local sync running properly and is not recommended for automated cloud refreshes.

Excel & Data Problems