Share via

Power Query / Get and Transform error - DataFormat.Error: External table is not in the expected format. Details: 0qwxynj4.XLS

Anonymous
2019-07-30T02:28:24+00:00
  • I make two new Workbooks, File1.xlsx and File2.xlsx, each with a small table with columns Name, Code, Number, and one row of data. The same problem occurs if this table is formatted as a Table. I save and close these files.
  • I open a new Workbook and save it in the directory above the files in the first step. I use New Query/From File/From folder and select the folder containing the two files in the first step.
  • Those two files are then listed. I click Combine Binaries (either in the column header or from the button on the top right).
  • I then get a yellow screen with the error:

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

Details:

    0qwxynj4.XLS

The name of the file changes each time I click Combine Binaries, and is always a randomly named .XLS file. I've tried filtering for Extension .xlsx, and I have checked for hidden files in that folder. Both files can be read fine when using New Query/From File/From Excel. The same problem occurs using .XLS formats, and with different files.

I'm using Windows 10 and Microsoft Excel 2016 MSO (16.0.4266.1001) 32-bit.

Any ideas what is causing this problem and how to fix it?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2019-08-02T14:10:20+00:00

    Please update us on your problem. Are you still facing the issue? Do you still require help?

    Was this answer helpful?

    0 comments
  2. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2019-08-01T02:37:57+00:00

    Did you get any solution to this issue?

    Was this answer helpful?

    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2019-07-30T07:11:29+00:00

    I can not reproduce the issue, I guess the reason is that I use the actual version of Excel. I suggest to update your Excel to the latest version, see screenshot below.

    Andreas.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-07-30T03:50:23+00:00

    Hi, 

    Thanks, but I've checked these pages before. This doesn't apply to my case, as I make the Excel files in Excel, and use very basic tables. If Excel doesn't produce valid Excel files, then there is a bigger issue than just my problem!

    The second suggestion only applies to single files, not to importing multiple files.

    Was this answer helpful?

    0 comments
  5. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2019-07-30T03:22:03+00:00

    Hi ##

    Greetings! I am Vijay, an Independent Advisor. There are two potential solutions here for this issue

    https://social.technet.microsoft.com/Forums/lyn...

    Do let me know if you have any more question or require further help.

    Was this answer helpful?

    0 comments