Custom Column adding dates using null values error

[Buddhima_Edi]

Hi All,

I have the following table in Power Query:

I am trying to create a custom column which satisfies the following criteria:

• If the [Completion Date] <= [Due Date] then the custom column row item is "Compliant" (else "Non-Compliant")
• If the [Completion Date] = null then the [Response Time] is to be added to the [Inspection Date] and if the resultant date is >= today's date then it is "Compliant" (else Non-Compliant)

I've had a go at it here:

• if [Completion Date] <= [Due Date]
  then "Compliant"
  else if [Completion Date] = null
         then if [Response Time] + [Inspection Date]*1 >= Date.From(DateTime.LocalNow())
                 then "Compliant"
                 else "Non-Compliant"
         else "Non-Compliant"

When I run the query I get a Custom Column with "Compliant" and "Non-Compliant" values along with error values for every "null" in [Completion Date], I suspect it's got something to do with the way I'm referring to today's date in the query.

Please let me know what you think and thank you in advance.

[v-frfei-msft · Accepted solution]

Hi @Buddhima_Edi ,

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

[Buddhima_Edi]

It works! Thank you for taking the time to help me!