Custom Column adding dates using null values error

Hi Anonymous ,

Add the custom column as below should work.

if [Completion Date] = null then if Date.AddDays([Inspection Date],[Response Time]) >= DateTime.Date(DateTime.LocalNow())
then "Compliant" else "Non-Compliant" else if [Completion Date] <= [Due Date] then "Compliant"
else "Non-Compliant"

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtW30DcyMLRQ0lEy0zeHMZFEjQ2UYnWIV2cC5CNkEWwTfWMDDCMNkcw0hDGRzTK0RJiFYGM3AsgGucvIEM0MZDeAzYCpMCNWBVXDKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Inspection Date" = _t, #"Due Date" = _t, #"Completion Date" = _t, #"Response Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inspection Date", type date}, {"Due Date", type date}, {"Completion Date", type date}, {"Response Time", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Completion Date] = null then if Date.AddDays([Inspection Date],[Response Time]) >= DateTime.Date(DateTime.LocalNow())
then "Compliant" else "Non-Compliant" else if [Completion Date] <= [Due Date] then "Compliant"
else "Non-Compliant")
in
    #"Added Custom"

Regards,

Frank