Forum Discussion

socialengaged's avatar
socialengaged
New Member
9 years ago
Solved

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)

 

  • 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

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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