[DataFormat.Error] External table is not in the expected format.

[Virtual_Ames]

Community,

I am having a problem importing files from a sharepoint folder (6 files that need to be brought together). I see these as .xls files in Sharepoint and therefore use:

     let
         Source = Folder.Files(.... /sharepoint location".......),

// no problem
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetExcelData", each Excel.Workbook([Content])),

//no problem - I am able to see all the data in PowerQuery

Everything looks fine - ready to go.

However, when I hit "Close and Apply" - the system returns an error:

     [DataFormat.Error] External table is not in the expected format.

The original files generated from a SQL query - and then the results of the query are pasted into an Excel file.... The orginal response that is being pasted is a tab delimited file - which may be causing the problem at some level.   

Is there a different approach I should be using to open these files? Instead of Excel.Workbook([Content}) is there a something else that will be more appropriate for a tab delimited file?

Thanks in advance.

[JordanJackson]

I experianced this error after exporting from third party, when following one of the comments from above I saw that even though I was selecting to export as an excel it was exporting to Html. I would suggest checking this as a troubleshoot method if anyone experiances this in the future.

[ankitpatira]

@Virtual_Ames Can you not use in-built sharepoint connector and connect that way ? Maybe it can get rid of that error using in-built connector.

[Virtual_Ames]

Ankit,

Thanks - using the SharePoint Folder approach has the same result.

I can see the folder, and see the .xls files. 

However, when I click the Content column to combine binaries, I get the same problem.

(Excel.Workbook(#Combined Binaries) is auto-generated upon clicking the double down arrows in the Content column shown above).

[augustindelaf]

@Virtual_Ames I have exactly same problem (also xls files from a SharePoint.Files request) !

The only solution that I found is to convert those files to .xlsx

Hope that helps !

Augustin de la Fouchardiere

MYPE 

https://www.mype-consulting.com/

[Andrea]

I have the same error, does somebody find a workaround?

[przemekkucia]

Same error,

In our case it seems that access restrictions create the problem. Even with full control given to me. After removing them it worked like magic. 

Sadly, if files have to be restricted - we haven't found any workaround. (Power Query for Excel also didn't work)

[Krishna09]

hi ,

how to remove access restrictions?

I am facing same error. In my case i have power automate flow. this flow creates .xls file everey day in sharepoint. 

and trying to connect to power query with schduled refresh.

but when I open xls file in browser and try to change from read to edit, it asks if it can be converted.

so i think if I can save file editable then there will be no issues.

so can any one guide me either how to remove access restrictions or save editable file in sharepoint with power automate flow?

[lme062]

I'm trying to access a JetReport .xlsx shared to me from a Sharepoint site

It also fails with "External table is not in the expected format"

Any one had experience with using shared excel files in PowerBI?

[Johanno]

I saw in another forum that someone wrote that this can occur when you export to Excel files from third party software. A workaround that worked for me is that if you open each file with Excel and save it, after this you can import it. It's not a good solution though if you have many files.

[grechill]

Hi @Johanno ,

Unfortunately it didn't work for me. Tried re-saving as xlsx, no success. Tried copying to new Excel file as data and saving as xlsx, no success. 

BUT

No wonder, saving as CSV works as there is no XML involved.