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

Expression.Error: We cannot convert the value null to type Number. mysteriously appeared

I'm working in an ERP reporting project and I have relatively complex PBI and PQ set with dozens of tables and processing steps.

 

Everything were fine until yesterday when I started to get error Expression.Error: We cannot convert the value null to type Number from several different phases of the processing steps. What makes no sense is that the phases where the error occurs is not how it normally is.

 

So, this is the end result from one of the tables I'm processing

janihenr_0-1619865978874.png

 

In the middle of the process eveything works fine. Here is the column item list I suspect is the reason for the errors:

janihenr_1-1619866080841.png

No problems there. This is the applied step #16

 

The next step #17 is about filtering some rows based on completely other column and which does not have anything to do with the salesorderItem column:

"= Table.SelectRows(#"Added invoicingPeriodWorkingDays", each [invoicingPeriodStarts] <= [invoicingPeriodEndDate])"

 

And now, when I look at the salesorderItem column after the step #17 I get :

janihenr_4-1619866504788.png

 

There it is. 

 

Anyone having seen similar behavior? Is this a memory related problem? Is there any way to get better insight what could cause this?

 

Jani

 

 

 

1 ACCEPTED SOLUTION
janihenr
Frequent Visitor

OK, I finally managed to solve this issue. Error message was in the end misleading.

 

The error "Expression.Error: We cannot convert the value null to type Number" happened already in earlier steps. For some reason it got expanded to all the columns at the later steps.

 

The root cause was unexpected values in the code list merged in earlier steps.

View solution in original post

3 REPLIES 3
janihenr
Frequent Visitor

OK, I finally managed to solve this issue. Error message was in the end misleading.

 

The error "Expression.Error: We cannot convert the value null to type Number" happened already in earlier steps. For some reason it got expanded to all the columns at the later steps.

 

The root cause was unexpected values in the code list merged in earlier steps.

View solution in original post

Vera_33
Solution Sage
Solution Sage

Hi @janihenr 

 

It might be UI did not load the error (limited rows), you can enable Column quality to see if any error and try to do some error handling?

 

Vera_33_0-1619874512762.png

 

janihenr
Frequent Visitor

Hi Vera,

 

Thanks for the comment.

 

Unfortunately column quality indicator displays no error as seen from the below:

janihenr_0-1619887703658.png

I have also tried to Remove Error, Remove Blank Rows, Keep Errors etc,

 

All I get is

janihenr_1-1619887814005.png

 

Normally you would get the errorneous rows visible and get more information what's going on but not in this case.

 

Jani

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors