cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
park
Frequent Visitor

Create custom column based on two other columns

Hello.

 

I would like to add to a formula to a custom column I have written for changing row values based on another column, but I only want to base it off one value.

 

Consider that I have two columns. Company and Type

CompanyTypeNew Column
PublixNFCH
WalmartERCH
PublixDFCH

I currently have this code = if Text.Contains([Company], "Publix", Comparer.OrdinalIgnoreCase) or Text.Contains([Company], "Walmart", Comparer.OrdinalIgnoreCase) then "CH" else [Type]

 

This would change all the Type to CH in a new column based on that company name. 

 

However, for this example. I would like for only the ones that read NF to switch to CH. I want to keep the ones that say ER the same. Besides ER, I also have 2 other values of Type, take example DF FF, I want these to say the same as well. 

 

So basically, I want my formula to do the same thing it is already doing, except ONLY apply to changing the type of NF in a new column to read CH.

 

It would look like:

CompanyTypeNEW Column
PublixNFCH
WalmartERER
PublixDFDF

 

Thank you. 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Use this

= if List.Contains({"Publix","Walmart"},[Company], Comparer.OrdinalIgnoreCase) and [Type]="NF" then "CH" else [Type]

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

Use this

= if List.Contains({"Publix","Walmart"},[Company], Comparer.OrdinalIgnoreCase) and [Type]="NF" then "CH" else [Type]

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors