Issue with Excel Spreadsheet (Sharepoint Online) as a Data Source

[mh94]

I am having an issue with a report which uses Excel spreadsheets as data source (scheduled export - 3 times a day).

Error: Data source errorDataFormat.Error: <ccon>File contains corrupted data.. </ccon>;File contains corrupted data.. The exception was raised by the IDbCommand interface. Table: XXXXX

The refresh worked fine until 14/05/2023. 

1. We have tested a manual export of a report. This has failed.
2. We have run a refresh with archived data and this has worked.

Error message is provided in two different places.

Online in the service:

Data source error: DataFormat.Error: <ccon>File contains corrupted data.. </ccon>;File contains corrupted data.. The exception was raised by the IDbCommand interface. Table: XXXXXX

- Error found in right of refresh highlighted with a "!".

Offline in desktop:

Expression.Error: The key didn't match any rows in the table.

Details:

Key=

Item=Page 1

Kind=Sheet

Table=[Table]

- Error found in transform query in the "source" step.

Any help would be appreciated. 

Thanks

[Anonymous]

Hi @mh94 

Did you update or delete some fields in your data source ? You can go to Power Query to check the error .

Common Power Query errors & how to fix them (exceloffthegrid.com)

Best Regards,
Community Support Team _ Ailsa Tao

[mh94]

Hi @Anonymous 

Thanks for your reply. 

It is a scheduled report, meaning I get the data everyday as an email attachment from the system. I use new data to refresh and archive the old data. I do not make any changes to data. I looked for any corrupt data in the new data and everything looks good to me. 

Unfortunately, Power Query error does not point me to any corrupt data.

[reggiebob]

Hi @mh94,

Were you able to find a resolution to this issue? Scheduled refreshed is giving me the same error message. Data hasn't changed and scheduled refresh has been working fine for several months until 5-26-23. Power Query does not point me to any corrupt data either.

[mh94]

Hi @reggiebob 

No resolution to this yet, my internal teams are trying to find a fix.

But, I managed to find a workaround using scheduled reports in CSVs format for now. And this works as expected which is a good thing to get the reports back to the usual refresh schdule without any hiccups!

Regards,

Mahesh 

[reggiebob]

Thank you @mh94. This workaround worked for me as well.

[mh94]

@reggiebob 

I'm glad it worked for you. 

May I ask what system you are using in the background?

I will update it here when I know what is causing this error.

[reggiebob]

Hello @mh94,

Same as you, SharePoint Online. 

Thanks again for your help!

[EugenioC]

Hello @mh94 ,

I have the same issue, please share the the solution if you find it.

Thanks.

[Anonymous]

Hi @mh94  @EugenioC ,

Is this copy of excel to sharepoint done by PowerAutomate?
I was having the same problem and I added one more step in the powerautomate flow and it worked.

[mh94]

Hi @Anonymous,

Thanks for your suggestion. 

In my case - I was using an Excel spreadsheet coming from ServiceNow Scheduled Reports which is stored in Sharepoint before using it in PowerBI.

Regards

Mahesh