Forum Discussion

Richard_100's avatar
Richard_100
Resolver I
4 years ago

Navigation step broke, rebuild changed code from Item/Kind to Name; why?!

Hello   Many of my power queries have started failing unexpectedly recently, showing this error: "Expression.Error: The key didn't match any rows in the table."       I isolated the error ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Richard_100 ,

 

Based on my test, in this part:

{[Item="Half Hour Stack",Kind="Sheet"]}[Data]

Item ,Kind ,Data are columns in your original source. So such error occurs when the Item column has been renamed as Name and the Kind column has been removed ,so that queries could not find the reference any more.

 

 

Below is an example:

After I removed Kind column , and renamed Title column:

 

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Richard_100's avatar
Richard_100
Resolver I
4 years ago

Hi Eyelyn

 

Thanks for looking into this for me.  I just had a notification saying this has been accepted as a solution; unfortunately it is not, sorry.  (Side note to the site administrators: marking something as solved when it is not *is not helpful*)

 

"Item" and "Kind" are not columns in the original datasource, they are the normal syntax generated by Power Query in the navigation step #2 in the code.  "Item" here means the name of tab in the Excel workbook specified in the source step, "Kind" specifies that it is a tab, i.e. that the the item named is a worksheet.  They are columns in your example because you added them as columns, but if you just connect to any spreadsheet you can see what Power Query generates automatically.

 

Normally, columns are not referenced until step #3 and beyond; my issue is the syntax required for step #2 to work had changed despite the file not changing in any way.  Other solutions I found, e.g. Chris Webb's blog, said this is usually because the name of the worksheet has changed.  That is a similar answer to yours (name of the worksheet, name of columns, the name of something changed) but I can guarantee that this is not the case for me, the file did not change in any way.

 

Are you able to explain or point me to a resource that explains more about code that is generated for the navigation step when connecting to files?  I just can't see anything that helps me in the M documentation.

 

Thanks

 

Regards

Richard