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

[Richard_100]

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 to the navigation step, and when I rebuilt the navigation step the auto-generated M code was different to what was working perfectly fine before.  

The code which used to work perfectly, and now does not:

let

    Source = Excel.Workbook(Web.Contents("https://our_address.sharepoint.com/sites/Rest_Of_Path/Filename.xlsx"), null, true),

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

Upon redoing the Navigation step, the autogenerated M code has changed to this, which works:

let

    Source = Excel.Workbook(Web.Contents("https://our_address.sharepoint.com/sites/Rest_Of_Path/Filename.xlsx"), null, true),

    Data1 = Source{[Name="Half Hour Stack"]}[Data],

Can someone explain what is going on please?  What has happened that code snippet #1 has stopped working?  I can see that I am referencing "Name" now instead of "Item" and "Kind" but I really want to understand the details of this.

Thanks

Regards

Richard

[v-eqin-msft]

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]

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