How can Power Query be changed to handle "External table is not in the expected format" error?

0
votes

I have an Excel sheet that pull data via Power Query from a SQL Server and merges the data to another sheet.

It worked fine until a week ago, where I started to get this error here:

DataFormat.Error: External table is not in the expected format

The error happens when I try to merge the query with a table in a different workbook.

I have updated from Excel 2013 to 2016 not that long ago, and I have gotten a compatibility warning that says that the queries might not be compatible with this version of Excel.

Is there a way to solve this problem?

1
excelmergepowerquery
Is the underlying SQL query the same or could there be additional columns? Other reason for this error may be e.g. text data in a field that you marked as numeric, are there any instances of that? To check go through the query steps and identify the first step where it breaks – Stachu

1 Answers

0
votes

The problem was due to the upgrade from Excel 2013 to Excel 2016. The query workbook was not able to be updated to the new version and became incompatible with the other workbooks as they were updated to 2016. The query most likely had a feature that was changed or removed in 2016 version.