Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I have the following table in Power Query:
I am trying to create a custom column which satisfies the following criteria:
I've had a go at it here:
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.
Solved! Go to 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
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
It works! Thank you for taking the time to help me!
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |