Forum Discussion

Lhansen435's avatar
Lhansen435
Copper Contributor
Jul 08, 2024
Solved

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...
  • 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.

Resources