Forum Discussion

karkar's avatar
karkar
Helper III
9 years ago
Solved

IN OPERATOR in PowerBi

    I want to perform the following in Power Bi..   If Diagnosis_code  in('125','126', '127', '128') then category=Proc1 else if Diagnosis_code in('225','226', '227', '228') then category=Proc2 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi karkar,



    Thanks for the reply. I need help with the syntax while creating a column in the query editor.

    Based on my test, the formula(M) below should work in your scenario. :smileyhappy:

    = if List.Contains({125,126,127,128},[Diagnosis_code]) then "Proc1"
else if List.Contains({225,226,227,228},[Diagnosis_code]) then "Proc2"
else null

     

    Regards

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

The syntax for if statements in the query editor is:

 

if *CONDITION* then *RESULT* else *ALTERNATE RESULT*

 

the "if-then-else" should be lower case

 

Also consider wrapping in "try-otherwise" to catch errors

 

You can learn more about these functions via a basic web search for Power Query language (M language)

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

I should mention that you will need to use multiple statements, for example:

 

if *CONDITION* then *RESULT* else if *CONDITION 2* then *RESULT 2* else if... else *ALTERNATE RESULT*