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

karkar's avatar
karkar
Helper III
9 years ago

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

 

Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
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

  • nirvana_moksh's avatar
    nirvana_moksh
    Impactful Individual
    7 years ago

    v-ljerr-msft - What if this "list" or Column is Alpha-Neumerical column, can we still use the List.Contains function?

  • karkar's avatar
    karkar
    Helper III
    9 years ago

    Hello V,

     

    Thanks for helping me .I have not yet tried this formulae and did it the manual way.

     New Field is the name of the field we are creating???

    NEW FIELD= 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

    Also I noticed you used the LIST.CONTAINS......is it  internal to Power BI ?

    Is LIST.CONTAINS POWER BI way of telling to read the values being passed? 

     

    Thanks

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi karkar,

       New Field is the name of the field we are creating???

      Yes, it is.

      Also I noticed you used the LIST.CONTAINS......is it  internal to Power BI ?

      Is LIST.CONTAINS POWER BI way of telling to read the values being passed? 

      LIST.CONTAINS is Power Query M formula language which is optimized for building highly flexible data mashup queries. It's a functional, case sensitive language similar to F#, which can be used with Power BI Desktop, Power Query in Excel, and Get & Transform in Excel 2016. :smileyhappy:

       

      Regards