How to Fix Power Query Errors When Loading an Excel XLS File
Question details
Power Query fails to import data and throws an error when attempting to load a specific Excel workbook.
- Product
- Excel Power Query
- Device & OS
- Windows PC / Mac
- Scenario
- Importing data into Power Query from an older Excel file saved in the binary .xls format.
- Observed behavior
- Power Query returns an error message and stops the data loading process when connected to the .xls file.
Verify that you have full access to the source workbook and that it is not currently locked, open in another background process, or being edited by someone else on your network.
Convert the Source File to .xlsx Format
Saving the outdated binary file as a modern Excel Workbook (.xlsx) resolves most formatting and compatibility issues with Power Query.
Power Query is highly optimized for the modern, XML-based .xlsx format. The older Excel 97-2003 binary format (.xls) often causes data parsing issues. Converting the file format natively in Excel is the most reliable way to fix this error.
Double-click the problematic .xls source file to open it directly in Excel.
Click on 'File' in the top-left corner of the ribbon menu and select 'Save As' from the sidebar.
In the file format drop-down menu, choose 'Excel Workbook (*.xlsx)' and save the file to your desired location.
Completely close the newly saved workbook to ensure it is not locked by Excel.
Open your main project file, navigate to your Power Query settings, and redirect your data source connection to the newly created .xlsx file.
Test for Source File Corruption
If converting the file does not work, the original workbook might be damaged. Testing it on another device helps isolate the issue.
Try WPS Office for Excellent Spreadsheet Compatibility
If you frequently encounter errors dealing with older Excel files or Microsoft Office limitations, consider switching to WPS Office. It handles legacy file formats natively and provides a smooth, resource-friendly experience for all your data management tasks.
Frequently Asked Questions
Why does Power Query have trouble reading .xls files?
The .xls format is a legacy binary format from Excel 97-2003. Power Query is specifically built and optimized to read modern, XML-based data structures (like .xlsx). Trying to parse the outdated binary format can lead to unexpected import errors.
Do I have to convert every single .xls file manually?
If you only have a few files, manual conversion is safest. If you process dozens of files regularly, you can write an Excel VBA macro or use a third-party batch conversion tool to automatically convert .xls files to .xlsx in a specific folder before Power Query loads them.
What if my converted .xlsx file still causes a Power Query error?
If the error persists after conversion, the data inside the file might contain structural issues, such as unsupported characters, corrupt tables, or merged cells that confuse Power Query. Try copying the raw data into a completely new workbook and importing that instead.
