Forum Discussion
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...
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.
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"- There are no 'null' values in my compensation column.
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.
- I added a file to my post.
Could you please share screenshot which includes row with error?
