Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Virtual_Ames
Microsoft Employee
Microsoft Employee

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

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.

 

 

 

10 REPLIES 10
JordanJackson
Advocate II
Advocate II

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
Community Champion
Community Champion

@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.

Ankit,

 

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

 

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

SharePoint Folder Import.JPG

 

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

 

SharePoint Folder - Combine Binaries error.JPG

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

@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/

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

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)

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?

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
Continued Contributor
Continued Contributor

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.

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.