cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kgiboin
Frequent Visitor

Expression.Error coming up and cant figure out why!

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_0-1645197350678.png

 

1 ACCEPTED SOLUTION
kgiboin
Frequent Visitor

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

View solution in original post

5 REPLIES 5
kgiboin
Frequent Visitor

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
Community Support
Community Support

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
Solution Supplier
Solution Supplier

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.
    MarkLaf_0-1645215102501.png
  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
Super User
Super User

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

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
AlexisOlson
Super User
Super User

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors