Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Ramendra
Helper IV
Helper IV

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

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?

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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.

5.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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.

5.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.