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...
Need Help
Tips and Tricks
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