'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Power Query Only Refreshes After Opening SharePoint Excel File
Question details
The user is experiencing an issue where a Power Query using Web.Contents to pull data from a SharePoint-hosted Excel file only refreshes after the file has been manually opened, while other files in the same folder work normally.
- Product
- Microsoft Excel / SharePoint
- Device & OS
- not provided
- Scenario
- Refreshing data via Power Query from a SharePoint-hosted Excel file that is automatically retrieved and saved by a Power Automate flow.
- Observed behavior
- The data refresh fails or hangs until the specific third-party file is manually opened in Excel, despite having identical permissions and folder locations as fully functioning files.
Ensure you have the necessary SharePoint access rights and verify that your Power Query connection credentials for Web.Contents are up to date.
Isolate the Issue with a Test Query
Create a separate, simplified query to determine if the problem is specific to the file's current query configuration or the file itself.
Testing a brand-new query against the problematic workbook helps rule out caching issues or complex transformation steps that might be causing the refresh failure.
Open Excel and create a new, blank spreadsheet.
Navigate to the Data tab, click on 'Get Data' > 'From Web', and enter the SharePoint URL for the problematic Excel file.
Load the data without applying complex transformations. Try refreshing this new query without opening the source file to see if the issue persists.
Investigate Power Automate and File Origins
Since the file originates from a third party and is processed via Power Automate, check for file metadata or format inconsistencies.
Verify SharePoint Permissions and Contact Support
Confirm there are no hidden permission conflicts causing the file to lock, and escalate to Microsoft support if necessary.
Try WPS Office for Seamless Spreadsheet Management
If you are frustrated by complex SharePoint syncing issues and Power Query refresh errors, try WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment without the overhead of complex background data connections.
- 1. Download and Install: Visit the official WPS Office website to download and install the free software on your device.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing Excel files without worrying about formatting loss.
- 3. Edit and Collaborate: Use the familiar interface to analyze data, create charts, and share documents effortlessly.
Frequently Asked Questions
Why does Power Query require opening a file to refresh?
This typically happens if the file is generated by a third-party system or saved by Power Automate without fully committing the required Excel metadata. Opening and saving the file manually writes the correct metadata, allowing Power Query to read it.
Can Power Automate corrupt Excel files during transfer?
Yes, if a flow extracts an attachment incorrectly or saves it with a mismatched extension, the file might become locked or unreadable by Power Query until it is manually opened, repaired, and re-saved.
How do I fix Web.Contents credentials in Power Query?
Go to Data > Get Data > Data Source Settings, select your SharePoint URL, click Edit Permissions, and ensure you are using Organizational Account credentials to sign in rather than Anonymous or Windows authentication.
