Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating Multiple Results in a column based on a combination of parameters from two other columns

Hello,

 

I am working on a Power BI report that looks at non-numeric data from a SharePoint Library. The report connects directly to the library and pulls related fiels "document name, document status, document classification, etc...).

 

I have a column called [Job Classification] and a Column named [Section]. I would like to create a column that will return different values based on IF statements form these two columns. Ultimately, Each Section has multiple job classifications. (e.g. Admin Services has Administrative Assistant 1, Administrative Assistant II as well as and Analyst I and Analyst II) and I want to be able to mark these as two different Job Series

 

For example, If the [Section] is "Administrative Services" and the [Job Classification] contains "Adiministrative assistant" then I would like to return the value "Admin Services Administrative Assistant Series".

 

I think the code is something like IF[Section]= Administrative Services AND IF[Job Classification] CONTAINS Administrative Assistant THEN, "Admin Services Administrative Assistant Series"

 

If possible in this column, I would like to include several similar statements. For example, after that statement I would like to Include ELSE IF[Section] = Regulatory Compliance AND IF[Job Classification] CONTAINS Compliance Agent THEN, "Regulatory Compliance Agent Series"

 

Thank you in advance for your assistance and input.

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    If you want to add a custom column with Power Query, try

    if [Section]="Administrative Services" and Text.Contains([Job Classification],"Administrative Assistant") 
then "Admin Services Administrative Assistant Series"
else if [Section]="Administrative Services" and Text.Contains([Job Classification],"Analyst") 
then "Admin Services Administrative Analyst Series"
else if [Section]="Regulatory Compliance" and Text.Contains([Job Classification],"Compliance Agent") 
then "Regulatory Compliance Agent Series"
else null

     

    You can use multiple else if ... then ... statement pairs to add more conditions.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Use a SWITCH(TRUE()...) statement like:

    Column = 
  SWITCH(TRUE(),
    [Section] = "Administrative Services" && SEARCH("Administrative assistant",[Job Classification],,0)>0,"Admin Services Administrative Assistant Series",
    <next logical condition>,<result if true>,
    <next logical condition>,<result if true>,
    <result if none of the conditions are met>
  )
  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     

    If you want to add a custom column with Power Query, try

    if [Section]="Administrative Services" and Text.Contains([Job Classification],"Administrative Assistant") 
then "Admin Services Administrative Assistant Series"
else if [Section]="Administrative Services" and Text.Contains([Job Classification],"Analyst") 
then "Admin Services Administrative Analyst Series"
else if [Section]="Regulatory Compliance" and Text.Contains([Job Classification],"Compliance Agent") 
then "Regulatory Compliance Agent Series"
else null

     

    You can use multiple else if ... then ... statement pairs to add more conditions.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.