We cannot convert the value null to type Number

[gvg]

Hi,

I know there is a number of threads with this title, however none of them provide clear answer. At least for my case.
I am getting this message when trying to sort a column with integer values. I do replace nulls before this step with integer value.  Previous steps do not produce any error. Column quality shows no error or empty values present. 

Following are the steps before sorting:

#"Replaced Value" = Table.ReplaceValue(ChangedType3,each _, each if Number.IsNaN([Sales history months]) or [Sales history months] = null then 24 
else [Sales history months],Replacer.ReplaceValue,{"Sales history months"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,24,Replacer.ReplaceValue,{"Sales history months"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value1",{{"Sales history months", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type3",{{"Sales history months", Order.Ascending}}),

What could be the problem, I wonder?

[Anonymous]

I'm telling you, the "or" operator doesn't not work nicely with comparing null values. The most efficient and error free way for you to write this is:

#"Replaced Value" = Table.ReplaceValue(ChangedType3,each _, each if Number.IsNaN([Sales history months]) then 24 else if [Sales history months] = null then 24

else [Sales history months],Replacer.ReplaceValue,{"Sales history months"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,24,Replacer.ReplaceValue,{"Sales history months"}),

    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value1",{{"Sales history months", Int64.Type}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type3",{{"Sales history months", Order.Ascending}}),

This will leave you with no errors to handle. 

if/then/else if

works fine with = null. The "or" operator does not like = null.

I assure you--give this code a try, you'll see what I mean!

--Nate

[Anonymous]

The error is actually being caused by this part of your if statement: 

[Sales history months] = null

PQ is saying "null is never equal to any number, so why am I comparing it to a number?"

Instead, write it as:

#"Replaced Value" = Table.ReplaceValue(ChangedType3,each _, each if Number.IsNaN([Sales history months]) or [Sales history months] is null then 24 else [Sales history months],Replacer.ReplaceValue,{"Sales history months"}),

[gvg]

What's wrong with the comparison using "=" to null? PQ does not complain about it.

[ImkeF]

Hi @gvg ,
this error is triggered by the 

Number.IsNaN([Sales history months])

 in your first step.
Null cannot be converted to a number an this triggers the error.
To overcome this you can wrap that statement in a try-otherwise error-handler: 

try Number.IsNaN([Sales history months]) otherwise true

[mussaenda · Accepted solution]

Hi @gvg,

as per my experience on this error, I am replacing error with text then filter the column with the replaced value.

So I can check why this value is giving error and adjust the applied steps.

Hope this helps.

[gvg]

@mussaenda ,

tried that. I even get error after I try to get all values loaded in the filter box after I replace errors with some text value:

Strangely enough Power Query displays null in the filter box, but still complains that it can not convert null to type Number.

[mussaenda]

If there is no any confidential data, you can DM me the file to check. 

Else,

The is a value that Power query cannot convert to the data type you chose which shows error.

[gvg]

Well, I managed to figure out what was the problem by deleting records until I saw the error. It looks like PQ does not replace all kinds of errors. Errors that were not captured by "Replace Errors" were those that arose as a result of merging tables. 

Anyway, thank you for the tip.