Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kgiboin
Helper I
Helper I

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
Helper I
Helper I

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
Helper I
Helper I

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors