Forum Discussion
Custom Column adding dates using null values error
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.
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
2 Replies
v-frfei-msftCommunity Support
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