none
DataFormat.Error: External table is not in expected format RRS feed

  • Question

  • I receive a [DataFormat.Error] External table is not in the expected format error while trying to load my query. My query looks to a folder that contains multiple folders with multiple excel file(300+) all in the same formatting, or supposed to be. It does not tell me where the table is located so that I can try to fix the problem. I need help troubleshooting this issue as it will most likely pop up again.
    Tuesday, September 5, 2017 6:11 PM

Answers

  • Hi Davis615.

    Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error. I met this errors permanently with my accounting system exports.

    The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files.

    You can check what file raises an error if you make a query to folder (Folder.Contents or Folder.Files, filter all xlsx files and then add a custom column with formula

    Excel.Workbook([Contents])
    If file has a mentioned error, this formula will return an error for this file (keep rows with errors), and you can find all files with problems.

    Maxim Zelensky Excel Inside

    Wednesday, September 6, 2017 3:59 PM

All replies

  • Hi Davis615.

    Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error. I met this errors permanently with my accounting system exports.

    The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files.

    You can check what file raises an error if you make a query to folder (Folder.Contents or Folder.Files, filter all xlsx files and then add a custom column with formula

    Excel.Workbook([Contents])
    If file has a mentioned error, this formula will return an error for this file (keep rows with errors), and you can find all files with problems.

    Maxim Zelensky Excel Inside

    Wednesday, September 6, 2017 3:59 PM
  • (Also posted here, which I've closed as a duplicate.)
    Wednesday, September 6, 2017 7:34 PM
    Owner
  • I found an solution for what I believe is the same problem.

    • Choose Text/CSV as the data connection type
    • Choose your system generated .xls file
    • import
    • you'll get an error
    • Edit the connection settings and choose Open File as HTML Page
    • You'll then likely have the ability to choose a table

    I have to use some Oracle generated .XLS file which Excel always tells me are potentially corrupted. I've never been able to use these as data sources w/in PBI until now. Hope this helps.

    • Proposed as answer by 7heEnd Monday, June 22, 2020 12:46 PM
    Tuesday, May 8, 2018 7:53 PM