External table is not in the expected format.

[BenFransen]

I have a data mutation process which is handled by Pentaho Kettle. Basically it reads several Excel-sheets, tidys up, rearranges, does some formatting, casting and sorting and writes it to an Excel-file again.

I'm facing issues with the generated Excel-file. When I want to load it I get the error "External table is not in the expected format.".

But: when I open the generated file with Excel and then retry to connect to it using Power BI Desktop it works. Only until the next time the Kettle workflow runs...

I'm assuming it has to do something with permissions but I can't seem to find a fix for this issue.

Any suggestions? Thanks!

[Anonymous]

To fix this :
Select the query in the fields : open "Edit Query" ; This will open power query editor.
Now go the navigation steps of that query in the right hand side. 
Step by step - review each steps in the navigation. In one of them you would find an issue (e.g Column change etc ) ; the step where you can open the "Table" click on it and the Table will open.
Then Apply,  Refresh in the main Power BI screen. the data will get refreshed.

BINGO !!

[TheoC]

I found the error is likely linked to the source data file(s) being in ".XLS" format.  By saving these down as ".XLSX" format, you should find the error is corrected.

[pmay]

This isn't possible when you're reading from external data sources, like a government site.

[v-sihou-msft]

@BenFransen

Actually this issue should be related to Excel since you have done nothing on Power BI side. Please remove the access restriction and try again. See a similar thread below:

https://community.powerbi.com/t5/Integrations-with-Files-and/DataFormat-Error-External-table-is-not-...

Regards,