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
There are no 'null' values in my compensation column.
SergeiBaklan
Jul 09, 2024Diamond Contributor
Another variant if Compensation=0 but Termination Date = null, when will be the same error. Anyway, in column(s) there null exist you shall add it condition similar to above for Compensation.
Or replace on some value.
- Lhansen435Jul 09, 2024Copper ContributorI added a file to my post.
- SergeiBaklanJul 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.