Unable to load Excel file because we couldnt understand its format error?

[mike_asplin]

Having spend days battling to get my model to refrsh because of some weird Odata eror i am now faced with this? Desktop modle refreshes fine with no issue. All the excel files are stored on sharepoint. This error messgae gives me no clue which file is an issue or what to do about it. Any help appreciated

Refresh completed with warnings.
Data source error: DataFormat.Error: We were unable to load this Excel file because we couldn't understand its format. The document cannot be opened because there is an invalid part with an unexpected content type. [Part Uri=/customXml/itemProps4.xml], [Content Type=application/xml], [Expected Content Type=application/vnd.openxmlformats-officedocument.customXmlProperties+xml].. Microsoft.Data.Mashup.ErrorCode = 10942. . The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

[krishnakanth240]

Hi @mike_asplin 

Reason might be one of Excel files in SharePoint is corrupted or not a valid .xlsx structure even if it opens in Excel

As Desktop it works where Power BI Service it fails on invalid internal XML parts (customXml)

So, in Power Query you can temporarily filter files one by one or add a column which will flag the file that has error

    ```M

    try Excel.Workbook([Content]) otherwise "Error"

    ```

Ensure file is not in .xls format and it shall be of .xlsx format and not partially corrupted from the upload

[mike_asplin]

All files were xlsx and they all work fine in the desktop so dont think thi would have helped. i opened and resaved every file and seems ot have fixed it so didnt try your suggestion. Thanks 

[ibarrau · Accepted solution]

Hi. The warning is saying that one of the files has an issue. I have read about this before. It's like the excel file wasn't saved properly in xlsx. Most users solve it opening the excel files from sharepoint at their excel desktop and save it again checking the format is xlsx.

You can read something similar in this post: https://community.fabric.microsoft.com/t5/Service/DataFormat-Error-We-were-unable-to-load-this-Excel...

Or this one: https://community.fabric.microsoft.com/t5/Service/We-were-unable-to-load-this-Excel-file-because-we-...

I hope that helps,