Expression.Error coming up and cant figure out why!

[kgiboin]

Hi,

I have a Query that just randomly stops working in this step. All its predecessor steps produce output, then all this one does is sort the  rows and it gives me this error. Its affected my entire workbook. I dont understand why this error is occurring. This workbook has several queries on it so could it be a memory problem? I have had the error pop up that I needed 64 bit Excel. I tried to refresh my data connections incase that was it.

Please help!

[kgiboin · Accepted solution]

Resolved:

This error occured because of my setup in previous steps. I had pulled files through from a sharepoint folder and someone had added a file which threw my navigation into disarray. In order to resolve this I changed how I got my files. Previously I had done some manual row removes, but I instead now am filtering on file path which resolved my issue. 

Hope this helps,

Kent

[v-jingzhang]

Hi @kgiboin 

Do previous comments help solve this problem? If so, kindly accept the appropriate reply as solution. More people will benefit from it. If you are still confused about it, please provide more details about the error and troubleshooting results based on suggestions in previous replies. Thanks.

Regards,
Community Support Team _ Jing

[MarkLaf]

To expand on previous comments and further explain the error:

When you connect to an Excel workbook and select which sheet(s) to load, the UI is actually performing two steps:

1. Source: Perform initial access function, which provides a table of all sheets, formatted tables, and named formulas. E.g.
   
2. Navigation: Drill into the sheet(s) selected in the UI with a row/key identifier, e.g. to access Sheet2 from above example: Source{[Item="Sheet2",Kind="Sheet"]}[Data]

The part in bold above is what the error is referring to as the "key" - the formula is looking for the row where [Item] = "Sheet2" and [Kind] = "Sheet".

To apply to your scenario, somewhere (and probably in the initial Source/Navigation steps as outlined above) your code is looking for a sheet named "Price template" and not finding it. So, either someone renamed or deleted the sheet in your source Excel. Remember M is case sensitive, so even someone "fixing" the "t" of the sheet name to "Price Template" would cause this error.

[watkinnc]

I bet your Transform File is looking for the Worksheet "Template File", and not finding it.

I wonder if you meant to filter for your worksheets in your actual main query? It looks like you are going straight from Source to the hidden files filter to Invoking the Transform File on all of the nested tables.

Or as @AlexisOlson said, it might just be a caching issue. You can clear the cache by going to Query Options, Data Load options (I think under Global options), and click Clear Cache.

--Nate

[AlexisOlson]

The error suggests to me that the problem occurs at the #"Invoke Custom Function1" step. I'm guessing the steps between that and #"Sorted Rows" are cached and would also throw an error if you clicked Refresh Preview.