Fix Excel Power Query DataFormat.Error When Refreshing SharePoint Workbooks
Question details
The user needs to resolve a DataFormat.Error that appears when Excel Power Query refreshes data from a SharePoint workbook.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A query connects to an XLSX file stored on SharePoint and is refreshed while the source workbook may be edited, saved, or temporarily locked.
- Observed behavior
- Power Query reports that the input is not a valid Excel document, often because it received an incomplete, temporary, inaccessible, or corrupted file.
Open the SharePoint source location and confirm that the query points to the intended .xlsx file, not a temporary or similarly named file. Check that the workbook has finished saving and that you can download and open it normally before refreshing.
Wait for SharePoint to finish saving, then refresh the Power Query connection
Use this first when the error occurs during active editing, AutoSave, or a recent upload of the source workbook.
Power Query can read an incomplete workbook while SharePoint or Excel is still saving it. Waiting for the source file to finish syncing often restores a valid XLSX response without changing the query.
In SharePoint, open the document library containing the source file and verify that the workbook is not being edited, uploading, or showing a pending sync status.
Close the source workbook or wait until its AutoSave and SharePoint sync activity finishes, then confirm that the file's modified time is stable.
In Excel, open the Data tab, select Refresh All, and wait for the Power Query connection to reload the SharePoint workbook.
Verify the SharePoint file path and isolate the source workbook
Use this when repeated refreshes fail or the query may be selecting the wrong file, a temporary file, or a damaged workbook.
Use a stable staging file and refresh after source updates
Use this for recurring refreshes where SharePoint workbooks are frequently edited or updated by automated processes.
Switch to WPS Office for Reliable XLSX Workbook Editing
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and saving XLSX workbooks with a familiar interface. It supports common Excel formats, making migration straightforward when you need to review or maintain SharePoint files without the full Microsoft Office suite.
- 1. Install WPS Office: Download and install WPS Office, then launch WPS Spreadsheets from the Start menu or Applications folder.
- 2. Open the SharePoint workbook: Download the completed XLSX file from SharePoint and select Open in WPS Spreadsheets to inspect or edit the source workbook.
- 3. Save in Excel format: Choose File > Save As, select the .xlsx format, and save the completed workbook back to the appropriate SharePoint folder.
Frequently Asked Questions
Why does Power Query say the SharePoint input is not a valid Excel document?
The source workbook may still be saving, locked, partially uploaded, temporarily unavailable, or corrupted. Wait for SharePoint synchronization to finish, then confirm that the file opens normally and refresh the query again.
How can I make sure Power Query selects the correct SharePoint file?
Open Data > Queries & Connections, edit the query, and inspect the Source step. For a SharePoint folder connection, filter the Name and Extension columns to the exact workbook and exclude temporary files such as ~$ files.
Can AutoSave cause a DataFormat.Error during a Power Query refresh?
Yes. AutoSave or SharePoint synchronization can briefly expose an incomplete file while Power Query is reading it. Refresh after the workbook finishes saving or use a staging folder containing only completed files.
What should I do if the downloaded SharePoint workbook will not open?
The source file may be damaged or incomplete. Download it again after the upload finishes, try opening it in Excel or WPS Spreadsheets, and replace or repair the workbook if it shows corruption warnings.
