Creating a conditional column with boolean (Yes/No) result for matching partial text matches

[Wahid777]

Hi

I've got a column listed as below and I'm trying to create a new column (with a Yes/No or True/False response) if some matches of certain keywords are found in the original column. Would "CONTAINS" be useful here? or should I create a new column with my Keywords [CEO, Chief Executive, Officer, CFO, Chief Financial Officer, Ambassador] and use Search?

Original column	New conditional column
Mr John is the CEO of x	Yes
Ms Lynn is the Vice president of Y	No
Tom is the ambassador of USA to the UK	Yes
Jerry is the Company Secretary of X Inc	No

Keywords

Ambassador

CEO

Chief Executive Officer

Chief Financial Officer

CFO

[v-stephen-msft]

Hi @Wahid777 ,

You could refer to Finding if a value in table 1 has a matching value in table 2.
You need a Keywords table by entering and there is no relationship between two tables.

Column 3 = 
IF (
    SUMX (
        'Keywords',
        FIND ( UPPER ( 'Keywords'[Keywords] ), UPPER ( [Original column] ),, 0 )
    ) > 0,
    "True",
    "False"
)

You can check more details from here.

Best Regards,
Stephen Tao

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

[AntrikshSharma · Accepted solution]

@Wahid777  You can use CONTAINSSTRING:

Column =
CONTAINSSTRING ( Wahid[Original column], "Ambassador" )
    || CONTAINSSTRING ( Wahid[Original column], "CEO" )
    || CONTAINSSTRING ( Wahid[Original column], "Chief Executive Officer" )
    || CONTAINSSTRING ( Wahid[Original column], "Chief Financial Officer" )
    || CONTAINSSTRING ( Wahid[Original column], "CFO" )

 

[negi007]

@Wahid777  I think you can go with the solution provided by @AntrikshSharma if you can't make changes to the alrady captured data. However, as a data analyst it is always recomended to have a clean data in the database itself. I suggest if possible please create a seperate column in your data itself for the position. For Example CEO or Chief Executive Officer are the same position. So ideally you would like to have one keyword for similar positions etc.