Forum Discussion
Custom column help needed
| 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"
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"}
12 Replies
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.
AntrikshSharma No error message. All the values fall in the Other bucket.
AntrikshSharma Just checked, no trailing or leading spaces.
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!BiAnalyst SWITCH isn't a function in M language.
Added sample data.
