Forum Discussion
Solved
IF function Need Help
I've been having this issue for days already and i hope someone can help me with this. = Table.AddColumn(#"Removed Columns", "Custom", each if[order_estimated_delivery_date] >0 then "Early Delive...
See attached file. It's the same code as earlier. Do note that the [order_estimated_delivery_date] column in the data you have shared is of type date but it is an integer in the data you showed on your initial post. Integer is what makes sense here so I have replaced the dates with some random numbers in the data for the example
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
See the code below and the file attached for a more elaborate solution where we first calculate the difference in days between the estimated and the actual delivery date. Note that the relevant steps in the M code below start at #"Added Custom1"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZNNrtswDISvEmT9gJAUKZK6SvAWEiUBBbrqouev/NMmcQEv7A+iNZoZPZ/3wYZTMFoP4azWNIIdU57Uc516/7r7mJxotEyC5JqAh2a03BCqk/W1pI+fP36PX2N7R3gAPVBvCEXyB8ACegLegReRA2yP3ggL/QW2AYACcP/+et4lRW80w1pojwEtK+MgFCBNLLimGliC2VhZa47cCfqo0CwMqOuYF6GgD1oy7EZQGE+QNwCpEL8D5JL2FUtU2oEU0nfwEsqqCqM5AgR1CGbn3D0m6Igs286MMagKBzRIbSbmFKl7wpqygvlVqD22fdYmVpJdwG7gP4CpCJy6dAdWYAsBfPf8Xaizd2nMvU3pw9Hm8MA8XRvLtLqmphm6cpZR1QlqXxmM0KSSMneja/R4xraCJbsAlgPQ0YVU0nZOpBMsXcfI+pLP6GsnDPEAYxg5mqXqMZqIpmko22+sNldfzcy9jmqWc2+9Bc+mdY3H1VE6Y1uFQ/sAVAhOsPu1Kc8nyKejqAc42/ISyuIYtOxCi4arpkPIzAd1qxFpTQkkXafwqLpuXfRetVWy4bXZzGD/d3Qld9wM0Q9ABeEAm8u6dVTsVdrj/p0dBXxz9PsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_id = _t, customer_id = _t, order_status = _t, order_purchase_timestamp = _t, order_approved_at = _t, order_delivered_carrier_date = _t, order_delivered_customer_date = _t, order_estimated_delivery_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"order_id", type text}, {"customer_id", type text}, {"order_status", type text}, {"order_purchase_timestamp", type datetime}, {"order_approved_at", type datetime}, {"order_delivered_carrier_date", type datetime}, {"order_delivered_customer_date", type datetime}, {"order_estimated_delivery_date", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Days_Diff", each Duration.Days([order_estimated_delivery_date]-[order_delivered_customer_date])),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if [Days_Diff] >0 then "Early Delivery" else if [Days_Diff] =0 then "On Time" else if [Days_Diff] <0 then "Late Delivery" else null, type text)
in
#"Added Custom"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |