Forum Discussion

moliana's avatar
moliana
New Member
3 years ago
Solved

Create a new column based on Multiple Conditions

I have a daily data set whereby the data shows total in dollars for customer purchases. I am trying to create a column in Power Query that shows the delivery fee from each customer purchase, which is calculated based on multiple conditions (Store, Delivery City, and Delivery method). If the purchase delivery method is Pickup, they are not charged the delivery fee so this is just $0.

 

If the store is 2354, then each delivery city has a different charge, (i.e. store 2354 charges a delivery fee of $1.99 when Delivery city = Warwick, but store 2386 charges $6.09 for delivery to Warwick, similarly store 2354 charges a delivery fee of $7.09 when Delivery City = Hamilton, but store 2386 charges a delivery fee of $8.09 when Delivery City = Hamilton)

See below data set example: 

 

 Branch IdDelivery CityDelivery methodOverall charged
2354PembrokeDelivery$47.74
2354WarwickDelivery$115.15
2386PembrokeDelivery$127.43
2386SmithsDelivery$130.82
2354PagetDelivery$138.50
2354WarwickDelivery$144.00
2386St.George'sDelivery$146.25
2354WarwickDelivery$157.06
2354HamiltonDelivery$158.97
2386SmithsDelivery$171.00
2386DevonshireDelivery$177.42
2354 Pickup$180.23
2354SmithsDelivery$184.71
2354SouthamptonDelivery$185.42
2386SmithsDelivery$190.73
2354St.George'sDelivery$207.16
2354PagetDelivery$211.12
2354HamiltonDelivery$211.29
2354PagetDelivery$211.94
2354PagetDelivery$225.18
2354DevonshireDelivery$279.60
2386HamiltonDelivery$288.27
2354DevonshireDelivery$300.17
2386HamiltonDelivery$335.40

 

I would like to create a conditional column to the right of this data but not sure which function to use to enter values based on multiple conditions? 

 

Appreciate the help 

Need Help
  • SamInogic's avatar
    SamInogic
    3 years ago

    Hi moliana,

    In Power BI, you can create a column using Power Query based on multiple condition with below steps:

    • Add new Custom column from query editor and used below expression as follows Screenshot

    Custom = Table.AddColumn(#"Changed Type", "Custom", each if [#"Delivery Method "] = "Pickup" then "$0" else if Number.ToText([Branch ID])="2354" and [#"Delivery City "]="Warwick" then "$1.99" else if Number.ToText([Branch ID])="2386" and [#"Delivery City "]="Warwick" then "$6.09" else if Number.ToText([Branch ID])="2354" and [#"Delivery City "]="Hamilton" then "$7.09" else  if Number.ToText([Branch ID])="2386" and [#"Delivery City "]="Hamilton" then "$8.09" else "")

     

    This will result as shown in below screenshot:


    If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


    Thanks!

    Inogic Professional Service Division

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    Hi moliana 

    Try this

    if [Delivery method] = "Pickup" then 0 else
if [Branch Id] = 2354 then (if [Delivery City] = "Warwick" then 1.99 else 7.09) else
 if [Branch Id] = 2386 then (if [Delivery City] = "Warwick" then 6.09 else 8.09) else null

     

    		 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.

     

  • SamInogic's avatar
    SamInogic
    Super User
    3 years ago

    Hi moliana,

    In Power BI, you can create a column using Power Query based on multiple condition with below steps:

    • Add new Custom column from query editor and used below expression as follows Screenshot

    Custom = Table.AddColumn(#"Changed Type", "Custom", each if [#"Delivery Method "] = "Pickup" then "$0" else if Number.ToText([Branch ID])="2354" and [#"Delivery City "]="Warwick" then "$1.99" else if Number.ToText([Branch ID])="2386" and [#"Delivery City "]="Warwick" then "$6.09" else if Number.ToText([Branch ID])="2354" and [#"Delivery City "]="Hamilton" then "$7.09" else  if Number.ToText([Branch ID])="2386" and [#"Delivery City "]="Hamilton" then "$8.09" else "")

     

    This will result as shown in below screenshot:


    If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


    Thanks!

    Inogic Professional Service Division

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/