Forum Discussion
Change to the way Power Query reads Excel files?
Hi There,
It turns out that the software we use to apply Sensitivity labels is affecting Excel metadata, rendering it corrupt from Power Query's point of view. As such, we have elected to transition away from using Excel files and will switch to CSV files.
Thanks.
Hi All,
Thank you for the feedback.
What is concerning me here is that there have been no changes to file, sheet or field names at all. In some cases, we are ingesting multiple excel files that all have a common name part, in other cases they are single files with multiple sheets, and in an isolated case it is a single file with a single sheet. Even locations have not changed.
I will test to see if being more explicit about field names and data types helps, but I feel a little dubious in the sense that we see everything we expect to see in Power Query - row counts, field names, data types all line up with what we see in the actual files.
To the best of our knowledge there have been no changes to the site name or authentication requirements.
Hi RyanW86,
Thank you powerbidev123 Murtaza_Ghafoor jason533, for your insights.
Since the workbook previews correctly in Power Query, I'd recommend identifying which Applied Step first fails during Close & Apply, as Microsoft recommends reviewing the query steps to pinpoint refresh errors. Also, re-save one of the affected workbooks in Excel to refresh workbook metadata, and if you're using Excel.Workbook, try enabling InferSheetDimensions=true, which Microsoft recommends for workbook dimension inconsistencies. Ensure the workbook isn't open or locked during refresh, as this can prevent Power Query from accessing the file.
Power Query Excel connector - Power Query | Microsoft Learn
Handling data source errors (Power Query) | Microsoft Support
Thank you.
Thank you.
I'm afraid we've tried these suggestions and while we have been able to get some of our files back to refreshing, there is still one scenario we can't quite work out.
We have a need to analyse multiple weekly excel exports in the same report. These Excel exports are stored in SharePoint (same folder - no changes). They are exported from a source system and settings have been consistent throughout. We ingest data from around 50 excel docs all structured identically and named consistently.
Where previously we were able to refresh this model without any trouble, this one also stopped working at the same time as all of this excel strangeness began (7 July). We've tried open and closing the workbooks, resaving them, restructuring our Power Query - pretty much everything we can think of. The problem I am finding is when we go through the navigation steps in Power Query, some of the files (when looking at them one-by-one) return the columns you'd expect - [Name], [Item], [Kind], [Hidden], but other files in the list are only returning [Name] and [Data].
When we try to process the whole lot of them, we are now consistently getting the message "DataFormat.Error: We were unable to load this Excel file because we couldn't understand its format. File contains corrupted data." However, each of the files, when opened individually, seems to work.
Again, I want to make it clear, that this whole process was working for well over a year and just stopped working earlier this month. Now this particular model appears to refresh in Desktop but will not refresh in the service. Again, for additional context, going through applied steps in the service, everything appears to work, but when I select the dropdown in the column that shows the file names, once the whole query has processed, it is there that I get the corrupted data message.Hi RyanW86,
Since the issue only shows up when the combined files are processed, try refreshing a smaller set of workbooks to see whether one workbook is triggering the error. If you're using Excel.Workbook, Microsoft recommends setting InferSheetDimensions=true for workbooks that may have incorrect worksheet dimension metadata, and opening and re-saving the workbook to fix the stored worksheet dimensions. It would also be useful to check the query's Applied Steps to identify the first step where the refresh fails and gather the detailed error information.
Thank you.
