Forum Discussion

gvg's avatar
gvg
Post Prodigy
4 years ago
Solved

We cannot convert the value null to type Number

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?

Need Help
  • mussaenda's avatar
    mussaenda
    4 years ago

    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.

8 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    4 years ago

    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's avatar
      gvg
      Post Prodigy
      4 years ago

      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's avatar
        mussaenda
        Community Champion
        4 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      gvg
      Post Prodigy
      4 years ago

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

      • ImkeF's avatar
        ImkeF
        Community Champion
        4 years ago

        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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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