Troubleshoot Excel Power Query’s “External Table Is Not in the Expected Format” Error
Question details
Determine why Excel Power Query reports an external-table format error when combining XLSX workbooks from a folder.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Combine multiple Excel 2016-created XLSX workbooks containing several worksheets and defined tables into one query result.
- Observed behavior
- Power Query returns “DataFormat.Error: External table is not in the expected format.” The user reports that opening and resaving the workbooks did not resolve the issue.
Identify the folder query, its sample-file helper queries, and the worksheet or defined table you intend to combine. Note the Excel version and build, and use a separate test folder containing only the workbooks you want to inspect.
Isolate the workbook or query step that triggers the format error
Use this diagnostic first to distinguish a specific unreadable file from a problem in the combine workflow.
Open the folder query in Power Query Editor and select the Source step under Applied Steps. Check the Name, Extension, and Folder Path columns for unexpected files, including files in subfolders.
At the file-list stage, filter Extension to .xlsx and exclude names beginning with ~$ in the Name column. Keep only the intended workbooks before the step that invokes the transform-file function.
Copy one intended workbook into a separate test folder. In Excel, use Data > Get Data > From File > From Folder, or the equivalent From Folder command in your Excel 2016 Get & Transform interface, and select that folder. Add the remaining workbooks incrementally to identify when the error appears.
Use Data > Get Data > From File > From Workbook, or the equivalent From Workbook command, to import the suspect XLSX file without combining it. Check whether the Navigator opens and whether selecting the intended worksheet or table loads successfully.

Check the sample-file query and worksheet or table selection
Use this approach when individual workbooks import successfully but the combined query fails.
Compare Excel and Power BI behavior and check Office updates
Use this diagnostic when the same files remain unreadable in Excel or when an equivalent folder query works in Power BI Desktop.
Use WPS Spreadsheet for everyday XLSX workbook editing
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday workbook editing tasks. It is not a fix for Excel’s Power Query combine-files error or a guaranteed replacement for the existing query workflow.

Frequently Asked Questions
Can this error occur even when every workbook was created in Excel?
Yes. Creating the files in Excel does not identify the cause of a later Power Query read failure. Import the suspect file through Data > Get Data > From File > From Workbook, then compare that result with the folder-combine query.
Should I remove Excel temporary files from the folder query?
Exclude them from the query rather than treating them as source workbooks. In Power Query Editor, filter the Name column to exclude names beginning with ~$ before invoking the combine transformation, and filter Extension to .xlsx.
How do I choose the correct table when the workbooks contain several sheets?
Open the generated Transform Sample File query and inspect its Navigation step. Confirm that it selects the intended named table with Kind set to Table, or the intended worksheet with Kind set to Sheet, and that the object exists in every source workbook.
What should I try if opening and resaving the files does not help?
Test the workbooks individually, then combine them incrementally in a separate folder. Locate the first failing Applied Step and compare the sample-file transformation against the file that triggers the error. The reported case already found resaving ineffective.
Can WPS Office repair the Excel Power Query combine-files error?
No. WPS Spreadsheet can be an alternative for ordinary XLSX viewing and editing, but it does not repair Excel’s Power Query engine. Keep the folder-combine troubleshooting in Excel or compare the workflow in Power BI Desktop.




