none
Data Type Conversion RRS feed

  • Question

  • Hi : In my "IF" statement below, one of the cells below is not populating (showing blank). I am guess its showing blank because of data conversion issue.  Any assistance would be greatly appreciated. Thanks

    Wednesday, August 14, 2019 10:29 AM

Answers


  • (and also null or false -> null)

    Now, I get it, thanks. That's why the result is null, rather then "".
    • Marked as answer by M.Awal Thursday, August 15, 2019 9:50 AM
    Wednesday, August 14, 2019 1:15 PM
  • You may also use such code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type  = Table.AddColumn(Source, "Type", each if Text.Contains([Comments], "Permacon") = true or List.Contains({17.51, 210.12}, [Final_Value]) then "Permaconn"
                                                                            else if Text.Contains([Comments], "T4000") = true or List.Contains({19.57, 234.84}, [Final_Value]) then "T4000" else "")
    in
        Type
    • Edited by Aleksei Zhigulin Thursday, August 15, 2019 10:21 AM
    • Marked as answer by M.Awal Thursday, August 15, 2019 10:33 AM
    Thursday, August 15, 2019 10:20 AM

All replies

    1. You should always start your if statement by handling the null value:
      if test1=null then do1 else if ... then ... else ...
    2. Obviously your data source in column "Comments" contains Blanks, I doubt it is related to any data convertion issue. If you would share your code, we might help more.
    Wednesday, August 14, 2019 11:01 AM
  • Hi,

    I guess, there are more decimal signs in values of Final_value column. I mean, the value is equal not precisely 234.84, but something like 234.84000051. So, in your if statement for Final_value column you may use conditions like this:

    Number.Round([Final_Value], 2, 2) = 234.84
    Wednesday, August 14, 2019 11:13 AM
  • If someone can explain the following:

    AFAIK null propagates:

    null or true -> null    (edited: true)

    true or null -> null    (edited: true)

    Text.Contains(null,"1")  -> null

    But

    Text.Contains(null,"1") or true -> TRUE

    I would have expected null



    • Edited by anthony34 Wednesday, August 14, 2019 12:37 PM
    Wednesday, August 14, 2019 11:48 AM
  • IMHO, it's ok, since:

    null or true -> true

    true or null -> true


    Wednesday, August 14, 2019 11:54 AM
  • yes, you are right, my mistake
    thanks Aleksei

    (and also null or false -> null)

    • Edited by anthony34 Wednesday, August 14, 2019 12:34 PM
    Wednesday, August 14, 2019 12:33 PM

  • (and also null or false -> null)

    Now, I get it, thanks. That's why the result is null, rather then "".
    • Marked as answer by M.Awal Thursday, August 15, 2019 9:50 AM
    Wednesday, August 14, 2019 1:15 PM
  • Thanks Anthony & Aleksei. After reading the comments from both of you. I replaced all the nulls with blanks in the "Comments" Column, where the if statement was referring to. After doing that, it kind of solved the problem. Strange behaviour of PQ how it handles null & blank when if statement.

    Still interested to see from Anthony's point of view to start the if statement with null statement.

    https://1drv.ms/x/s!Amc8fiGpDxekg3XymxsgK8smiLWi

    Thursday, August 15, 2019 9:48 AM
  • You may also use such code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type  = Table.AddColumn(Source, "Type", each if Text.Contains([Comments], "Permacon") = true or List.Contains({17.51, 210.12}, [Final_Value]) then "Permaconn"
                                                                            else if Text.Contains([Comments], "T4000") = true or List.Contains({19.57, 234.84}, [Final_Value]) then "T4000" else "")
    in
        Type
    • Edited by Aleksei Zhigulin Thursday, August 15, 2019 10:21 AM
    • Marked as answer by M.Awal Thursday, August 15, 2019 10:33 AM
    Thursday, August 15, 2019 10:20 AM
  • Learn something new today, using List function inside if statement.

    Thanks

    Thursday, August 15, 2019 10:33 AM
  • you can even simplify

    Type  = Table.AddColumn(Source, "Type", each if Text.Contains([Comments], "Permacon") = true or List.Contains({17.51, 210.12}, [Final_Value]) then "Permaconn"
                                                                            else if Text.Contains([Comments], "T4000") = true or List.Contains({19.57, 234.84}, [Final_Value]) then "T4000" else "")

    in

    Type  = Table.AddColumn(Source, "Type", each if Text.Contains([Comments], "Permacon")  or List.Contains({17.51, 210.12}, [Final_Value]) then "Permaconn"
                                                                            else if Text.Contains([Comments], "T4000")  or List.Contains({19.57, 234.84}, [Final_Value]) then "T4000" else "")
    because
    Text.Contains(..., ...) 
    is already a logical result so you do not need Text.Contains() = true



    • Edited by anthony34 Thursday, August 15, 2019 10:58 AM
    Thursday, August 15, 2019 10:56 AM
  • You still have an issue, for example your row 49 generates an error.

    It comes from the fact [Comments]=null and Final Value is not in your list of test, and then you get:

    if null or false then ... else ...

    which means

    if null then ... else ...

    which generated the error

    To avoid it, you could do:

    Type = Table.AddColumn(Source, "Type", each
        if ( [Comments]<>null and Text.Contains([Comments], "Permacon") ) or List.Contains({17.51, 210.12}, [Final_Value])
        then "Permaconn"
        else
            if ( [Comments]<>null and Text.Contains([Comments], "T4000") ) or List.Contains({19.57, 234.84}, [Final_Value])
            then "T4000"
            else "",
        type text
    )

    it works because

    ( [Comments]<>null and Text.Contains([Comments], "Permacon") )
    will never be null by construction
    • Edited by anthony34 Thursday, August 15, 2019 11:13 AM
    Thursday, August 15, 2019 11:02 AM
  • Hi Anthony: I was interested to see what was the alternative solution that's why I didn't include the step where I replaced null values with blanks. Once that step is being added back, if statement should work as desired.
    Thursday, August 15, 2019 11:12 AM
  • Also like the second solution, as it is bit shorter. No need to use true
    Thursday, August 15, 2019 11:15 AM
  • you can even simplify

    Type  = Table.AddColumn(Source, "Type", each if Text.Contains([Comments], "Permacon") = true or List.Contains({17.51, 210.12}, [Final_Value]) then "Permaconn"
                                                                            else if Text.Contains([Comments], "T4000") = true or List.Contains({19.57, 234.84}, [Final_Value]) then "T4000" else "")

    in

    Type  = Table.AddColumn(Source, "Type", each if Text.Contains([Comments], "Permacon")  or List.Contains({17.51, 210.12}, [Final_Value]) then "Permaconn"
                                                                            else if Text.Contains([Comments], "T4000")  or List.Contains({19.57, 234.84}, [Final_Value]) then "T4000" else "")
    because
    Text.Contains(..., ...) 
    is already a logical result so you do not need Text.Contains() = true



    Unfortunately, we can't do it with this data sample. As you mentioned before, null or false --> null. So, if [Comments] = null and [Final_value] <> 17.51 or 210.12 first line gives us null. Since it's not a logical value (true/false), evaluation of if statement breakes. So, we get the error:

    That's why "= true" is needed.

    Thursday, August 15, 2019 12:56 PM