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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Virtual_Ames
Employee
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
Responsive Resident
Responsive Resident

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors