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

[Anonymous]

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.

[v-jingzhang · Accepted solution]

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.

[Greg_Deckler]

@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>
  )