Power Query Error "The key didn't match any rows in the table"

[MrAtkinson]

Hi All, 

I have Power Query pulling 5 files from a folder and combining them. These files have been created through Power Query themselves and are a merge of other files. This worked great until i realised that I had saved them all as .cvs files meaning it got rid of all of the queries in the background and meant they wouldnt auto update, which is what I need it to do. 

I then rebuilt them all in the exact same way as previously but saved them as .xlsx files. Now it doesnt work and gives me the error of 

An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table. Details: Key= Item=IIM Kind=Sheet Table=[Table]

I have done some googling and most of the people are saying that it happens when the name of a file has changed. I have recreated from the beginning so not sure how this can be the case as all of the files are newly created. I have even taken out any of the column name changes in the original files just in case its this but no luck. 

Any ideas?

[v-cgao-msft]

Hi @MrAtkinson ,

Given the transition from .csv to .xlsx files, it's crucial to ensure that the expected sheet names are present in the .xlsx files. .csv files do not have sheet names, but .xlsx files do, and Power Query might be looking for a specific sheet name to perform its operations.
you might find the following link helpful:
Chris Webb's BI Blog: Understanding The “The key didn’t match any rows in the table” Error In Power ...
The #1 Mistake You're Making in Your Queries

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[watkinnc]

Check your sample file name.

--Nate 

[johnbasha33]

@MrAtkinson 

It seems like you're encountering an error in your Power Query transformation when trying to combine Excel files (.xlsx) that were recreated from scratch. The error message you're seeing, "Expression.Error: The key didn't match any rows in the table," suggests that there might be an issue with the way Power Query is trying to match the files or sheets during the transformation process.

Here are some troubleshooting steps and potential solutions you can try:

1. **Check File Names and Sheet Names:**
- Ensure that the file names and sheet names in your folder match exactly what you're expecting in your Power Query transformation.
- Even small differences in names, such as extra spaces or different capitalization, can cause issues.

2. **Verify Data Structures:**
- Double-check the structure of your Excel files, including the data layout and column headers.
- Ensure that all files have the same structure and that the columns you're trying to combine match across all files.

3. **Refresh Data Manually:**
- Try refreshing the data manually in Power Query to see if the error persists.
- This can help identify if the issue is specific to automatic refreshes or if it occurs regardless of how the data is refreshed.

4. **Check Query Dependencies:**
- Review the queries in your Power Query Editor and check for any dependencies or references between queries.
- Ensure that all referenced queries are available and properly configured.

5. **Clear Cache and Refresh:**
- Clear the cache in Power BI Desktop and then try refreshing the data again.
- Sometimes, cached data or query results can cause unexpected errors.

6. **Test with Sample Files:**
- Create a set of sample Excel files with simple data structures and test your Power Query transformation with them.
- This can help isolate whether the issue is specific to your original files or if it occurs with any set of Excel files.

7. **Review Power Query Steps:**
- Review the Power Query steps in your transformation process and look for any potential errors or inconsistencies.
- Check for any conditional logic, filters, or transformations that might be causing the issue.

8. **Check for Updates:**
- Ensure that Power BI Desktop is up to date with the latest updates and patches.
- Sometimes, issues can be resolved by updating to the latest version of Power BI Desktop.

By systematically troubleshooting and testing different aspects of your Power Query transformation process, you should be able to identify and resolve the underlying issue causing the error. If the problem persists, consider reaching out to the Power BI community forums or Microsoft support for further assistance.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!