Refresh Failing with an error I can't interpret unexpected content type

[masplin]

Model refreshes fine in desktop, but on the service get this

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].Cluster URI:WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.netActivity ID:90ff93d0-10b0-4597-8333-ea0a5ef47f16Request ID:160cdff7-fb6d-98a1-a2c2-16738a9e6ec5Time:2022-01-11 13:58:34Z

Model is fed by mostly excel sheets stored on Sharepoint.

Any clues appreciated

Mike

[collinq]

HI @masplin ,

This could be from a difference in protocols or a difference in versioning or some data that doesn't match up.  Please make sure that your gateway is updated and that you have the latest version of Power BI as well.  You might want to confirm that you have data types on all of your fields (and none are set to "any").

I would also clear your cache, then refresh the report and publish and check it out.

[masplin]

Hi.  All the data sources are on sharepoint so I dont have a gateway

I updated power BI version, cleared the cache and checked al lthe columns have a specific data type and made no difference. 

I have 4 sources of Sharepoint data.  I went through them turning off the "include in report refresh" flag.  Turning them all off it completed and i found i coudl trun 3 of them on, but the 4th one which is the transactions failed.  This query is a folder query containing several files so I tried each of them and all failed.  I recreated the data files by copying and pasting values only into a fresh excel sheet in case something buried in the sheet by the exporting system.  

Any ideas what to try next? 

Thanks

Mike

[collinq]

Hey @masplin ,

If this works on 3 of the 4 files, I think that means it is the file itself.  I have seen this sort of issue resolved by any of these individually or combined :

• Upload the file to a new location and test that it can work from there.
• Save it to "csv" or "xml" and then back to "xlsx" (to clear anything random/corrupted in it)
• REname the file entirely and see if that works
• Confirm the permissions on the 4th file are allowing a connection
• Make sure that the file is not open/in edit mode by anybody
• Make sure that the file is in the same language as you are using
• Confirm that there is no column with totally unexpected data in it (ie. a date field with a letter)

Let us know!

[masplin · Accepted solution]

So it turns out to be that classic MS error message that is completely misleading.  Having spent hours turning each file on and off (28 in total) I narrowed it down to one query.

The issue seems to be there was a column that imported as general data type, but is full of numbers. I formatted it as a date, but this through an error. I later removed this column so I didn't notice the error and there was no error reported by Power BI desktop.  To fix this I had to first format the column as a whole number then as a date. Now theservice refresh works.

So in fact it was nothnig to do with any of the things you helpfully suggested, but was an error in the query path that Desktop was able to deal with, but the service could not.

Thanks so much for your input, but hopefulyl this wil lhelp others. Basically you have to check every step in the query even if you dont actually load that column.

Mike

[masplin]

To clarify I did also have some excel sheets generated by system export that had additional tabs maybe containing other facets that the service didnt like. however even after cut and pasting vlaues into new sheets I was stil lfailing on the above issue.