Change to the way Power Query reads Excel files?

Hi RyanW86 

The “data has been corrupted” message is often not literal file corruption. In Power Query, it can mean the binary stream or workbook structure isn’t what the connector expected.

However do look out for the below pointers that may have caused this issue:

If you’re combining multiple Excel files from SharePoint, a single bad workbook can cause the whole refresh to fail. Common culprits:

• a hidden temp file accidentally included
• a workbook that opens with a repair message in Excel
• a file with a broken table object
• a workbook saved while still open/locked
  
  

Sometimes the SharePoint.Files / Web.Contents pattern breaks because of:

• site URL changes at the tenant level
• authentication token/session weirdness
• connector cache mismatch
• a library or file version that Power Query can see in Desktop but the service resolves differently

RyanW86 

The error you are encountering "The key didn't match any rows in the table"    means the file name is changed and you are source path is harcoded in Power Query.

So you need to follow these steps and hopefully you will not encounter this problem again.

1-Check Sheet and Table Names

Open your Excel file and look at the names of your sheets or tables. Power BI looks for the exact same names every time. If a sheet name changed (for example, from "Sheet1" to "Data"), change it back, or update the name in your Power BI steps.

2-Pick the First Sheet Automatically

If you are putting together many different files and the sheets inside them have different names, Power BI gets disorganized. You can change your Power Query steps to always pick the very first sheet of any file, no matter what it is named.

3-Check the Column Headers

Look at the titles at the top of your Excel columns. They must be exactly the same as before. If a column title is misplaced, spelled differently, or has extra spaces, Power BI will stop working and show an error.

If this helps, ✓ Mark as Kudos | Help Others

Thanks for sharing the update. Changes to how Power Query interprets Excel files can have a real impact on existing queries, especially when workbooks rely on consistent sheet names, ranges, or inferred data types. One thing I've found helpful is making the import steps as explicit as possible—for example, selecting named tables instead of entire worksheets and setting column data types manually rather than relying on automatic detection. That tends to make queries more resilient when the underlying connector behavior changes in future updates.

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.