Custom column help needed

[csolanky]

segment1	Category
2000	Air Transportation
2000	Donor Tissue Typing
2000	Ground Transportation
2000	Hospital & Physician Costs
3000	Air Transportation
3000	Hospital & Physician Costs
3000	Recovery Supplies
3000	Serology

Need help with this custom column. It seems to work but as soon as I add the "and [segment1]" part, it does not work. segment1 is a text field. I have tried using quotes like "2000". Still doesn't work.

if List.Contains({"Hospital & Physician Costs","Surgeon Fees","Ground Transportation","Air Transportation","Serology","Donor Tissue Typing","Recovery Supplies","Other Organ Expenses"}, [Category]) and [segment1]=2000
then "Direct Organ"
else if List.Contains({"Hospital & Physician Costs","Surgeon Fees","Ground Transportation","Air Transportation","Serology","Donor Tissue Typing","Recovery Supplies","Historical Year Discards","Other Tissue Expenses"}, [Category])
then "Direct Tissue"
else "Other"

[dufoq3]

Hi @csolanky, provide sample data in usable format (read note below if you don't know how) and expected result based on sample data please.

[csolanky]

Added sample data. 

[BiAnalyst]

Hey @csolanky  could you try swicth instead?

SWITCH (
TRUE,
List.Contains({"Hospital & Physician Costs","Surgeon Fees","Ground Transportation","Air Transportation","Serology","Donor Tissue Typing","Recovery Supplies","Other Organ Expenses"}, [Category]) && [segment1] = 2000, "Direct Organ",
List.Contains({"Hospital & Physician Costs","Surgeon Fees","Ground Transportation","Air Transportation","Serology","Donor Tissue Typing","Recovery Supplies","Historical Year Discards","Other Tissue Expenses"}, [Category]), "Direct Tissue",
"Other"
)

might do the trick!

[AntrikshSharma]

@BiAnalyst SWITCH isn't a function in M language.

[csolanky]

@BiAnalyst I get an error that SWITCH not recognized. I belive there is no built-in Switch function in Power Query. I could be wrong though.

[AntrikshSharma]

@csolanky What is the error message? Always mention that in your question. Check if the column name "segment1" doesn't have any trailing or leading spaces or any other unwanted characters.

[csolanky]

@AntrikshSharma No error message. All the values fall in the Other bucket.

[AntrikshSharma]

@csolanky How is there no error message? your question title says custom column error.

[csolanky]

Wrong choice of words for the title. Truly I needed help with teh custom column. Thanks for heads up!

[AntrikshSharma · Accepted solution]

@csolanky your code works fine for me I am not getting any errors.

Categories is just a step I created to remove redundancy of the same list

= {"Hospital & Physician Costs","Surgeon Fees","Ground Transportation","Air Transportation","Serology","Donor Tissue Typing","Recovery Supplies","Other Organ Expenses"}

[csolanky]

Thanks. Must be something else that is whacky on my end. Thanks for reponse. 

[csolanky]

@AntrikshSharma Just checked, no trailing or leading spaces.