Forum Discussion

Lhansen435's avatar
Lhansen435
Copper Contributor
Jul 08, 2024

Power query - error null values in date field

I am receiving the following error.  I believe it is due to a null in the termination date field.   The employees receiving the error have no term dates coded (still actively employed)

 

Expression.Error: We cannot convert the value null to type Logical.
Details:
Value=
Type=[Type]

 

The column that has the errors is a custom formula.  Below is the formula

 

if [Compensation] > 0 then if List.Contains({"A","B","C","D","H","M","N","R"},[Primary Status]) then if [Entry Date]> #date(2023,03,31) then "2-Current Year Elig" else "3-Prior Year Elig" else if List.Contains({"S","Y"},[Primary Status]) then "1-Min Age Svc" else if List.Contains({"Z","Q"},[Primary Status]) then "6-Special Status" else 0 else if [Termination]> #date(2023,03,31) then "4-current year term with 0 comp" else "5-Prior Year Term"

excel
Formulas and Functions
Power BI
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 09, 2024

    Lhansen435 

    Thank you. [Termination] is null. To correct

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
              {"Birth Date", type date}
            , {"Hire Date", type date}
            , {"Entry Date", type date}
            , {"Secondary Entry", type date}
            , {"Tertiary Entry", type date}
        }),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            if [Compensation] > 0 and [Compensation] <> null
            then
                if List.Contains({"A","B","C","D","H","M","N","R"},[Primary Status])
                then
                    if [Entry Date]> #date(2023,03,31)
                    then "2-Current Year Elig"
                    else "3-Prior Year Elig"
                else
                    if List.Contains({"S","Y"},[Primary Status])
                    then "1-Min Age Svc"
                    else
                        if List.Contains({"Z","Q"},[Primary Status])
                        then "6-Special Status"
                        else 0
            else
                if [Termination]> #date(2023,03,31) and [Termination] <> null
                then "4-current year term with 0 comp"
                else "5-Prior Year Term")
in
    #"Added Custom"

    Please check attached.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 08, 2024

    Lhansen435 

    It happens if [Compensation] is null. Replace null to on zero or expand conditions for [Compensation]

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
              {"Entry Date", type date}
            , {"Termination", type date}
            , {"Compensation", Currency.Type}
            , {"Primary Status", type text}
        }),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            if [Compensation] > 0 and [Compensation] <> null
            then
                if List.Contains({"A","B","C","D","H","M","N","R"},[Primary Status])
                then
                    if [Entry Date]> #date(2023,03,31)
                    then "2-Current Year Elig"
                    else "3-Prior Year Elig"
                else
                    if List.Contains({"S","Y"},[Primary Status])
                    then "1-Min Age Svc"
                    else
                        if List.Contains({"Z","Q"},[Primary Status])
                        then "6-Special Status"
                        else 0
            else
                if [Termination]> #date(2023,03,31)
                then "4-current year term with 0 comp"
                else "5-Prior Year Term")
in
    #"Added Custom"

Resources