I want to add a custom column based on two different coulmn data in my power BI query.

[Ramendra]

I have a column A and column B. I want to add new column C like-
if coulmn B contains specific text1 and column A contains another specific text2 then put text X in column C else if coulmn B contains specific text3 and column A contains another specific text4 then put text Y in column C else if coulmn B contains specific text5 and column A contains another specific text6 then put text Z in column C and so on.

Can some one please help me with the formula?

[v-lili6-msft · Accepted solution]

hi  @Ramendra 

In edit queries, you could use the logic of this formula:

if( Text.Contains([Column A],"text1") and Text.Contains([#"Column B#(lf)"],"text2")) 
then "X" 
else if( Text.Contains([#"Column B#(lf)"],"text3") and Text.Contains([Column A],"text4"))
then "Y" 
else if( Text.Contains([#"Column B#(lf)"],"text5") and Text.Contains([Column A],"text6")) 
then "Z" 
else null

and here is sample pbix file, please try it.

Regards,

Lin

[az38]

Hi @Ramendra 

use switch() statement like

Column = SWITCH( TRUE(),
SEARCH("aa", [colA]) > 0 && SEARCH("bb", [colB]) > 0, "X",
SEARCH("cc", [colA]) > 0 && SEARCH("dd", [colB]) > 0, "Y",
"something else"
)