Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Multiple Ifs in Query Editor

This has been tough for me to figure out. I think it stems from the differences in DAX vs whatever is used in the Query editor. I need to create a custom column in the query editor that uses multiple Ifs. This is what I have written but I know the syntax is not correct. I do not think the conditional column will allow me to add all the conditions I need, so I prefer to learn how to do this with a custom column if statement

 

if([Cost_Centers.Cost_Center]=OR(109,6044,6045,6046,6047,6048,7020,7021,7022,7023,7024,7025,7026,7027,7028,7029,7030,7031,7032,7033,7034),
"Coffee",
if([Cost_Centers.Cost_Center]=OR(7001,7002), "Tea",
if([Cost_Centers.Cost_Center]=OR(1,40,91,93,94,129,150,217,228,287,291,349,364,381,407,436,477,479,500,501,530,534,537,556,558,566,583,588,591,597,623,629,710,778,779,780,781,782,783,784,785,786,787,789,790,791,792,793,794,795,796,798,801,809,810,815,877,878,881,901,1609,1995,7009,7010)
,"Soda")))

 

Need Help
Tips and Tricks

1 Reply

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    8 years ago

    Using M, I would put the lists in a variable. This would come in the advanced editor, After the first line "Let", and before your first step, probably  "Source"

     

    Let
CoffeeList =({109,6044,6045,6046,6047,6048,7020,7021,7022,7023,7024,7025,7026,7027,7028,7029,7030,7031,7032,7033,7034}),

TeaList =({7001,7002}),

SodaList =  ({1,40,91,93,94,129,150,217,228,287,291,349,364,381,407,436,477,479,500,501,530,534,537,556,558,566,583,588,591,597,623,629,710,778,779,780,781,782,783,784,785,786,787,789,790,791,792,793,794,795,796,798,801,809,810,815,877,878,881,901,1609,1995,7009,7010}),

    You can then use List.Contains to check if it is in that list. The first argument is the list, the second is the value.

     

    The if statement needs to start with "each if", the seperated by "else if", and finally an "else" for other values like:

     

     #"New If Column" = Table.AddColumn(#"STEP", "Column Name", 
each if List.Contains([Cost_Centers.Cost_Center], CoffeeList then "Coffee"
else if List.Contains([Cost_Centers.Cost_Center], TeaList then "Tea"
else if List.Contains([Cost_Centers.Cost_Center], SodaList then "Soda"
else "Other" 
),

    Replacing 

    #"STEP"

    with the name of the previous step