Search for word in multiple columns

[jcastr02]

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?  

[v-zhangti]

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.

[sevenhills]

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. 

[TomMartens]

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

[smpa01 · Accepted solution]

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