Concatenate IF with OR operator

[socialengaged]

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)

[v-jiascu-msft]

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

[socialengaged]

Thanks! Fixed, sorry for delay. 

[v-jiascu-msft · Accepted solution]

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

Best Regards!

Dale