Forum Discussion
Lhansen435
Jul 08, 2024Copper Contributor
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...
- Jul 09, 2024
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.
Lhansen435
Jul 09, 2024Copper Contributor
I added a file to my post.
SergeiBaklan
Jul 09, 2024Diamond Contributor
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.
- Lhansen435Jul 09, 2024Copper Contributor
Thank you. It is working at this time.
- SergeiBaklanJul 09, 2024Diamond Contributor
Lhansen435 , thank you for the confirmation, glad to help