Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Nathanael04
Frequent Visitor

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 Delivery" else "null"
(if[order_estimated_delivery_date] =0 then "On Time" else "null"
(if[order_estimated_delivery_date] <=0 then "Late Delivery" else "null")))

 

the problem that im having is that instead of having 3 results is im only having 1. I tried also with " cunditional column" but the same results.

 

Need help for this.m4 4.9 task.jpg

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@Nathanael04 

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

 

SU18_powerbi_badge

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.

     

 

 

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

@Nathanael04 

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"

 

 

SU18_powerbi_badge

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.

 

AlB
Super User
Super User

@Nathanael04 

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

 

SU18_powerbi_badge

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.

     

 

 

AlB
Super User
Super User

@Nathanael04 

The code is correct.  The function Table.AddColum is being passed 3 arguments so perhaps you have not copied it in full or the error is coming from somewhere else. If you can share the query I can have a look

 

SU18_powerbi_badge

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.

 

 

AlB
Super User
Super User

Hi @Nathanael04 

 

= Table.AddColumn(#"Removed Columns", "Custom", each
      if [order_estimated_delivery_date] >0 then "Early Delivery" else
        if [order_estimated_delivery_date] =0 then "On Time" else
          if [order_estimated_delivery_date] <=0 then "Late Delivery" else null ) 

 

SU18_powerbi_badge

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.

 

Expression.Error: 2 arguments were passed to a function which expects between 3 and 4.
Details:
Pattern=
Arguments=[List]

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.