Power Query Problems

Troubleshoot Excel Power Query’s “External Table Is Not in the Expected Format” Error

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the source file list

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.

2
Filter before combining

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.

3
Test a small folder

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.

4
Import the failing file directly

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.

Isolate the workbook or query step that triggers the format error
Resaving is not a confirmed fix: The reported workbooks were created in Excel 2016, and opening and resaving them did not help. If a workbook imports successfully on its own, investigate the generated combine queries rather than repeatedly resaving it.
Free Microsoft Office alternative

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.

Open and edit XLSX workbooks used in your folder-combine workflow.Supports Microsoft Excel formats, including .xlsx and .xls.Familiar spreadsheet tools help ease migration for everyday editing.A lightweight option for routine spreadsheet work outside Power Query.
microsoft office alternative - wps office

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.