Forum Discussion

RyanW86's avatar
RyanW86
Regular Visitor
1 month ago
Solved

Change to the way Power Query reads Excel files?

My colleagues and I have just this week encountered a problem reading and refreshing Excel files in both the service and Power BI Desktop. For context, the Excel files are stored in SharePoint; we ha...
Need Help
  • RyanW86's avatar
    RyanW86
    1 month ago

    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.

Murtaza_Ghafoor's avatar
Murtaza_Ghafoor
Icon for Super User rankSuper User
1 month ago

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