Forum Discussion
Power ParticipantRefresh Failing with an error I can't interpret unexpected content type
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
Power ParticipantHi. 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
Super UserHey 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
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.