M code syntax for connecting to Excel files - difference between sheet and table (Navigator step)

[Draginko]

Hey all,

I have a quite weird question - is there somewhere some documentation, blog, video, or anything explaining M code for connecting to excel? Especially if you want to modify the code for switching between table in excel file and sheet as a whole? (Basically covering "Navigator" step in UI).

I am really struggling with this for quite some time, and every time I am googlig like hell to find some explanation, and in the end I will end up with creating new query with new connection.

The reason is, that we are predominantly using excel files as a data source, many times those excel files are outcome of some excel-based power query scripts. Unfortunatelly we are often changing many dataflow processes, especially for new projects, and result is that sometimes the data are in shape of a table (in case of PQ outputs) and sometimes just a sheet (exports from other systems). This wouldnt be a big deal, with the exception of getting this expression error (details can vary, based on file or my unsuccessful amendment of the code):As our files are huge and we have a lot of queries, every refresh or preview takes dozens of minutes, sometimes even hour, I prefer to do all the changes via m code, so I can avoid unintentional waiting on preview for change to take effect. Also, it would be easier just to modify the code, instead of e.g. transforming sheet to table in the source file (from time perspective). However I am not able to grasp this little piece of code, how to modify it properly so it will work, based on my requirements, for instance this code: 

This code should work, as the file I am connecting is just a sheet, with name Sheet1, without any table, and it is still not working. For different file, also sheet, is it working flawlessly.

So, any idea, or advice how to grasp this small topic? Or am I missing something? Everything is welcomed 🙂
PS: sorry for maybe a wider context than necessary

[amitchandak · Accepted solution]

@Draginko , There can be a few reasons

like

https://blog.crossjoin.co.uk/2020/04/06/understanding-the-the-key-didnt-match-any-rows-in-the-table-...

https://stackoverflow.com/questions/58420438/how-to-resolve-power-bi-error-the-key-didnt-match-any-r...

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table...