Forum Discussion

Wahid777's avatar
Wahid777
Frequent Visitor
5 years ago
Solved

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

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 columnNew conditional column
Mr John is the CEO of xYes
Ms Lynn is the Vice president of YNo
Tom is the ambassador of USA to the UKYes
Jerry is the Company Secretary of X IncNo

 

Keywords
Ambassador
CEO
Chief Executive Officer
Chief Financial Officer
CFO

 

 

Need Help
Tips and Tricks
  • AntrikshSharma's avatar
    AntrikshSharma
    5 years ago

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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    AntrikshSharma
    Community Champion
    5 years ago

    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's avatar
      negi007
      Community Champion
      5 years ago

      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.