“The key didn’t match any rows in the table” is one of the most common errors in Power Query.
The way it’s always explained is that it is caused when the original data changes format or name, or has been deleted.
However, there is a third, related option, which I’m writing about here because it stumped me for a while.
Firstly, you might want to have a look at this great blog by Chris Webb, which also contains lots of other suggestions and scenarios in the comments.
My scenario wasn’t in there though. In my example, I had a query I had made by combining two other queries. One of these precedent queries began with a “Get data from folder” step. The other just connected to a data file. Neither of the queries appeared broken. There was no ugly yellow triangle, and the data appeared to be complete at the end of the query.
However, when I tried to load or refresh the data, I got the dreaded message.
Because the predecessor queries LOOKED ok I spent a while investigating the combining process as I assumed it had gone wrong there somehow. I renamed the previous queries to see if they’d get picked up automatically. They did. There was no issue connecting to the previous queries.
After a lot of searching, and even trying Copilot (which mostly seemed to suggest summaries of Chris’s blog), I eventually went back and took a closer look at the folder query. And then I noticed that the error message popped up when I tried to filter one of the fields. Even though it didn’t result in an error in the query itself. Strange!
This eventually led me to realise that although the query had looked complete, some of the folder data hadn’t actually loaded. Then I took a closer look at the folder and found the reason:
Someone had added another folder into the overall folder, and that folder had inconsistent files in it.
So, if you have this error, obviously have a look at whether any files have been renamed or deleted, but don’t forget to check whether any files have also been added!
I’ve added a summary of this blog as a comment to Chris’s blog, in case it helps someone else.
I’m a Power Query trainer, as well as being an all-round Excel geek. I offer a range of power query training options, and I always include a bit on errors and troubleshooting!
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.