IF value of a cell in col is empty return null value of col1 with custom condition accordingly

[Anonymous]

Hello everyone,

I'm still new with the power query and PowerBI, and I was wondering if I can return the null value in column 1 with custom conditional column according to the grade in column 2.

Initially I was doing it like this, but something felt off about it......

= Table.AddColumn(#"Added Custom1", "Role", each if [C1] = "P1" or [C1] = "P2" or [C1] = "P3" or [C1] = "P4" or [C1] = "P3/P4" then "Executive" else if [C1] = "P5" or [C1] = "P6" then "Staff" else if [C1] = "P7" or [C1] = "P8" then "Principal" else if [C1] = "P9" or [C1] = "P10" then "GM" else if [C1] = "P11" or [C1] = "P12" or [C1] = "P11/P12" then "SGM" else if [C2] = "P1/P2" or [C2] = "P1" or [C2] = "P2" [C2] = "P3" or [C2] = "P3/P4" or [C2] = "P4" then "Executive" else if [C2] = "P5" or [C2] = "P5/P6" or [C2] = "P6" then "Staff" else if [C2] = "P7/P8" or [C2] = "P8" then "Principal" else if [C2] = "P9" or [C2] = "P10" then "GM" else if [C2] = "P11/P12" or [C2] = "P11" or [C2] = "P12" then "SGM" else null)

[wdx223_Daniel]

= Table.AddColumn(#"Added Custom1", "Role", each {null,"Executive","Staff","Principal", "GM","SGM"}{List.PositionOfAny({{"P1","P2","P3","P4"},{"P5","P6"} ,{"P7","P8"},{"P9","P10"},{"P11","P12"}},{[C1],[C2]},0,(x,y)=>List.ContainsAll(x,Text.Split(y??,"/")))+1})