Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Search for word in multiple columns

Hello,

 

I am trying to have a conditional column or custom column that would show yes or no if a specific word comes up in multiple columns.

 

Example  if the word "Apple" is contained  in col A, B, or C, then "Yes" if not, "No"

 

Follow up question - Is there case sensitivity with the search of the word Apple?  

Need Help
  • smpa01's avatar
    smpa01
    5 years ago

    jcastr02  insensitive with SEARCH, sensitive with FIND

     

    Column = switch(true(),search("apple",'Table'[Column1],1,0)=1&&search("apple",'Table'[Column2],1,0)=1&&search("apple",'Table'[Column3],1,0)=1,"yes","no")

     

     

4 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    jcastr02  insensitive with SEARCH, sensitive with FIND

     

    Column = switch(true(),search("apple",'Table'[Column1],1,0)=1&&search("apple",'Table'[Column2],1,0)=1&&search("apple",'Table'[Column3],1,0)=1,"yes","no")

     

     

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    Hey jcastr02 ,

     

    next to the approach smpa01 already presented, I tend to use Power Query to create calculated columsn, assuming we are have a dataset in import mode or the function will be folded, as the function I propose will not fold.

     

    Create a column using Power Query usint the function List.Contains( { [ col1 ] , ... , [col4] } , ... )

    This article by Chris Webb is a must read: https://blog.crossjoin.co.uk/2017/01/22/the-list-m-functions-and-the-equationcriteria-argument/

     

    I prefer calculated columns created by Power Query as these columns will be compressed.

     

    Hopefully this helps to tackle your challenge.

     

    Regards,

    Tom

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    5 years ago

    For case sensitivity, you have to use FIND, ContainsStringExact. 

    SEARCH is NOT case sensitive 

     

    For your needs, you can try this: (I used "APPle" to show case sensitive)

     

    Column = IF(AND(ContainsStringExact('Table'[Column1], "APPle"), ContainsStringExact('Table'[Column2], "APPle"), ContainsStringExact('Table'[Column3], "APPle")), "Yes", "No")

     

     

    Hope this helps!

     

    FYI: I agree with "Power Query is more efficient way", due to data compression and other internal implementaion. 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, jcastr02 

    1.  Create a new table, as shown in the figure, including various possibilities.

    2.  Create a new calculated column in desktop, and use IF and SEARCH functions.

    Column = IF(SEARCH("apple",'Table'[A]&" "&'Table'[B]&" "&'Table'[C],1,0),"yes","no")

    Best Regards,

    Charlotte Zhang

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