[DataFormat.Error] when refreshing queries in Excel

Question

I have a "master" excel file stored on a SharePoint site which has a number of queries fetching data from other Excel files stored in the same SharePoint folder.

Recently, when I pressed "Refresh All", I got an error message stating:

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.

Immediately after clicking OK, the data seemed to update as expected. The owner of the source workbook was updating data and had autosave turned on when I went to refresh the queries.

I should also note that each "Source" file from which the data is updated have permissions set up such that only myself and one other individual can view/update the file on SharePoint.

Any ideas on what might have caused this error?

Answer 1

Hi @DataViz369,

It seems this is not a common issue per my test. However, we can work together to narrow down and resolve the situation.The error message "[DataFormat.Error] The input couldn't be recognized as a valid Excel document" may occur when refreshing queries in Excel if the source file is open and being edited by another user. In this case, the source file may not be saved in a valid Excel format until the editing is complete, causing the error message to appear. It is recommended to wait until the editing is complete and the source file is saved before refreshing the queries.

Another possible cause of this error is a corrupted file or a missing file reference in the query. In this case, you may need to recreate the query or repair the file.

To resolve this issue, you can try the following steps:

1.Make sure all files are in xlsx format.

2.Check if the source file is still available and accessible to you.

3.Check if the source file is in the correct format and not corrupted.

Reference: https://support.microsoft.com/en-us/office/handling-data-source-errors-power-query-7b0b4a6e-9402-4f80-ab66-2239dbc7d6e8

Hope the information can help you. And if there have any unclear or misunderstanding, please feel free to post back and we’ll continue to help you all the time!

Your understanding and patience will be highly appreciated! Hope you have a good day and keep safe!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I think this IS a common issue.. I have a Power Automate process where I "get email attachments", and "Create File on SharePoint" as xlsx. File opens fine, all tables there etc. But shows corrupt error in PowerQuery in Office 365.. Works again after I open & save the xlsx on my computer.

Post on this from 2017. No resolution there either.

Such alert could appear when you try to use Power BI connector on Excel file. It's understandable if the source file is corrupted and can't be opened in Excel. However, it looks strange if Excel opens the file in question and shows nothing wrong.

https://techcommunity.microsoft.com/discussions/excelgeneral/the-input-couldnt-be-recognized-as-a-valid-excel-document-/50475