cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Concatenate IF with OR operator

hello everyone! 

im almost new with powerbi, and i find it a great tool! I have an issue thou, I hope you can help me find out a solution: 

 

when i create a Custom Query -> Custom Column, I add the below formula to create a Custom.Topic column:

 

= if Text.Contains([Custom], "cloud", Comparer.OrdinalIgnoreCase) then "Cloud"
else if Text.Contains([Custom], "nuvola", Comparer.OrdinalIgnoreCase) then "Cloud"
else null

 

This formula basically analizes the [Custom] column text and if it finds the word "cloud" it adds Cloud in the new column.

 

Now I want to add different words to the new column, so that

 

IF it finds the word "cloud" or "computing" it adds "Cloud" to the new column, CONCATENATE "|", OR IF it finds the word "analytics" or "data" it adds the word "Analytics" to the new column (separating both words with a pipe) 

 

I tried with this formula:

 

= (if Text.Contains([Custom], "cloud", Comparer.OrdinalIgnoreCase) then "Cloud"
else if Text.Contains([Custom], "nuvola", Comparer.OrdinalIgnoreCase) then "Cloud"
else null)&(if Text.Contains([Custom], "analytics", Comparer.OrdinalIgnoreCase) then "Analytics"
else if Text.Contains([Custom], "dati", Comparer.OrdinalIgnoreCase) then "Analytics"
else null)

 

But it basically concatenate the 2 words only when it finds both. So the formula does not work. Do you know how I can obtain the below wanted output (it's an example)? 

 

Custom.Topic (Column)

Cloud

Analytics

Cloud|Analytics

Cloud

Cloud|Analytics

 

Thanks a lot for help!
Eugenio (SEO)

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @socialengaged,

 

Try this formula please.

 

=if (Text.Contains([Words], "cloud") or 
    Text.Contains([Words], "computing")) 
   and  
   (Text.Contains([Words], "analytics") or 
   Text.Contains([Words], "data") ) 
   then "Cloud|Analytics" 
   else if 
   (Text.Contains([Words], "cloud") or 
   Text.Contains([Words], "computing")) 
  and 
   not  (Text.Contains([Words], "analytics") or 
  Text.Contains([Words], "data") ) 
  then "Cloud" 
  else if  
  not (Text.Contains([Words], "cloud") or 
  Text.Contains([Words], "computing")) 
  and  
 (Text.Contains([Words], "analytics") or 
  Text.Contains([Words], "data") ) 
  then "Analytics" 
  else null

Concatenate IF with OR operator.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
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

3 REPLIES 3
Microsoft
Microsoft

Hi @socialengaged,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

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

Thanks! Fixed, sorry for delay. 

Microsoft
Microsoft

Hi @socialengaged,

 

Try this formula please.

 

=if (Text.Contains([Words], "cloud") or 
    Text.Contains([Words], "computing")) 
   and  
   (Text.Contains([Words], "analytics") or 
   Text.Contains([Words], "data") ) 
   then "Cloud|Analytics" 
   else if 
   (Text.Contains([Words], "cloud") or 
   Text.Contains([Words], "computing")) 
  and 
   not  (Text.Contains([Words], "analytics") or 
  Text.Contains([Words], "data") ) 
  then "Cloud" 
  else if  
  not (Text.Contains([Words], "cloud") or 
  Text.Contains([Words], "computing")) 
  and  
 (Text.Contains([Words], "analytics") or 
  Text.Contains([Words], "data") ) 
  then "Analytics" 
  else null

Concatenate IF with OR operator.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors