Log In
Register
Home
Active Member
November 5, 2020
Offline
Hi, I have taken over a report that has a long power query in it. Initially it loads the data from an excel file, then does a load of changes to the data before the data gets used in the report.
Now I know need to change the input file: different file name, different location. The data in sheet is the same data with exactly same column names.
I have tried various methods to change the source data - changing the M code in the advanced editor and changing the data source settings.
Whatever I do I a message along the following:
Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=back_end_source
Kind=Sheet
Table=[Table]
* back_end_source is the name of the xls and the sheet. It was the name of the sheet in the old data source.
Anybody have any ideas?
Thanks
Andrew
See Answer
July 16, 2010
Offline
Hi Andrew,
Is the sheet name in the new file the same as the old file? This is important too and the likely cause of the error.
Mynda
Active Member
November 5, 2020
Offline
Hi Myanda, The sheet name is the same.
I did actually manage to "persuade" it to work. I noticed that I was missing 1 column. I added the missing column and then instead of the existing error I got a message saying something like "XYZ not found". The missing column did exist. I edited the M code in the advanced editor and deleted any reference to the said column that was missing. This happened a couple of times till it all ran through.
It doesn't fit well with me that this can be a solution.
Surely I'm missing something?
Any help appreciated 🙂
Thanks
Andrew
July 16, 2010
Offline
Glad you found it, Andrew. That is the only solution. Power Query is expecting the same file layout and has these references hard coded, so if there's even a space or capitalization out of place, the query will break.
Answers Post
