Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

case when or if with strings in Power Query

I am new to Power BI but have used SQL. Both M and DAX are making me feel stupid!! Thanks for letting me vent.

 

I have a column that groups students into 8 different types. I want to use an if statement or case when statement to bring that down to 4 and put the 4 new labels into a new column. I am pretty sure I need to do with in Power Query. This is what I have so far. What am I doing wrong?

 

"Trad Fleer Grad", each if [College]='ADULT' then 'Fleer'
else if [College]='GRAD' then 'Graduate'
else if left([College],1)='V' then 'Nondegree'
else 'Traditional')

 

Also, is there an "in" command, such as

if [College] in ('TRAD','5YTRD','PTTRAD') then 'Traditional'

 

 

Need Help
  • mussaenda's avatar
    mussaenda
    3 years ago

    Hi Anonymous ,

     

    I understand that in SQL, you should be using ' '.

    But in Power Query, it should be " ".

     

     if

    [College] = "ADULT"

    then "Fleer"
    else

     

    if [College] = "GRAD"

    then "Graduate"
    else

     

    if

    Text.Start( [College], 1) = "V"

    then "Nondegree"
    else

     

    "Traditional"

     

     

    -----------

    if

    List.Contains( {"TRAD","5YTRD","PTTRAD"}, [College] )

    then "Traditional"

    else

     

    YourConditon

     

    Hope this helps

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    if List.Contains({"TRAD","5YTRD","PTTRAD"},[College]) then "Traditional"

  • mussaenda's avatar
    mussaenda
    Community Champion
    3 years ago

    Hi Anonymous ,

     

    I understand that in SQL, you should be using ' '.

    But in Power Query, it should be " ".

     

     if

    [College] = "ADULT"

    then "Fleer"
    else

     

    if [College] = "GRAD"

    then "Graduate"
    else

     

    if

    Text.Start( [College], 1) = "V"

    then "Nondegree"
    else

     

    "Traditional"

     

     

    -----------

    if

    List.Contains( {"TRAD","5YTRD","PTTRAD"}, [College] )

    then "Traditional"

    else

     

    YourConditon

     

    Hope this helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you both. I asked one question and learned a lot: about the spaces, " instead of ', List.Contains, Text.Start, and those { }. I am on a roll.