Forum Discussion
AutoSave leading to File contains corrupt data?
Community SupportI made some progress with my investigation and here my findings:
When I get the "File contains corrupted data" error when refreshing a report, I first check if the file opens (from the SharePoint library) in Auto-Save mode. If yes I switch Auto-Save off. If it isn't or if switching it off doesn't make a change, I use the File, Check for Issues, Inspect Document option and remove everything. This fixes it in most of the cases, but I had one case so far where it didn't.When checking the query steps, I found this one
= Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true)
Which was added automatically. What is causing this step to be added between "Filtered Rows" and "Invoke Custom Function" when combining binaries? I removed that step and refreshed the query - file no longer contains corrupted data!
Now the error is back, with the same file, which had NOT been modified since in the SharePoint library where it resides.
It's not in Auto-Save mode.
The inspector does not find any metadata, hidden XLM data, etc. that could be removed.
The "Filter hidden files" step is not present in the query.This is frustrating. How do I troubleshoot this? The file is definitely ok (well, I assume so because it worked fine last time and the file hasn't been modified since then). Thanks for any pointers!
Pinning down the issue by removing and adding files one by one to the filtered list of binaries to be combined I found the culprit. It was the largest Excel file in the set, with 210k rows. I could however not identify any "corrupted data" in that file. So I decided to split it into two junks of 100k and 110k rows respectively, maintaining ALL rows. This fixed the issue this time. I'll however not sing victory yet... why would splitting a file in two without altering any data remove the "corrupted data"? Weird... it can't be some row limit, because most of the time it had worked in the past (or was "fixed" by removing auto-save, xml data, hidden file filter...).
v-chuncz-msftToday I got the issue again. 3 binaries to be combined, all around 30 MB in size, slightly over 100k rows each.
Refresh failed because file contains corrupted data but now way to tell which one. I opened the three of them in Excel desktop from the SharePoint library, switched AutoSave off, saved them manually. After that, no more corrupted data. Let's see what comes next...
Hi there Beat,
I've been experiencing the same issue recently with a large Excel file in a SharePoint site. First time it happened I reloaded the file in the query editor all the way from the beginning and this seemed to fix it, but only for a week. The file is not as big as yours, less than 100k rows, but your suggestion of manually opening it on the desktop and saving seemed to have fixed the issue for now. Hopefully that does it but I'm a bit confused on what's going on in the first place.
Still investigating. Sometimes I can reproduce the error, sometimes I can't. There must be more to it than just Auto-Save but I can't figure out what it is.
