Create a new column based on Multiple Conditions

[moliana]

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 Id	Delivery City	Delivery method	Overall charged
2354	Pembroke	Delivery	$47.74
2354	Warwick	Delivery	$115.15
2386	Pembroke	Delivery	$127.43
2386	Smiths	Delivery	$130.82
2354	Paget	Delivery	$138.50
2354	Warwick	Delivery	$144.00
2386	St.George's	Delivery	$146.25
2354	Warwick	Delivery	$157.06
2354	Hamilton	Delivery	$158.97
2386	Smiths	Delivery	$171.00
2386	Devonshire	Delivery	$177.42
2354		Pickup	$180.23
2354	Smiths	Delivery	$184.71
2354	Southampton	Delivery	$185.42
2386	Smiths	Delivery	$190.73
2354	St.George's	Delivery	$207.16
2354	Paget	Delivery	$211.12
2354	Hamilton	Delivery	$211.29
2354	Paget	Delivery	$211.94
2354	Paget	Delivery	$225.18
2354	Devonshire	Delivery	$279.60
2386	Hamilton	Delivery	$288.27
2354	Devonshire	Delivery	$300.17
2386	Hamilton	Delivery	$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 

[SamInogic · Accepted solution]

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 crm@inogic.com

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

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

[AlB]

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.