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. * 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 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 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 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. Thanks Myanda.
Details:
Key=
Item=back_end_source
Kind=Sheet
Table=
