extract specific text from Colum

[ShajiFiroz]

I need to extract specific text from a column and output the result in a conditional column.
For example, I need to extract the word "Marketing"  from the following sentence in the column, "This is for Marketing team to take action". Then, output "Marketing" in the newly created conditional column. 

Looking forward to hearing from experts. Thank you

[PhilipTreacy · Accepted solution]

Hi @ShajiFiroz 

Please download my sample PBIX with this data and code.

It's not clear if you are looking for only a single string "Marketing" or if you want to look for several different strings.  Always best if you supply some sample data.

If you are looking for several strings you can create a Calculated Column like so

Column = SWITCH (

    TRUE(),
    
    CONTAINSSTRING ( 'Table'[TextCol], "Marketing" ), "Marketing",

    CONTAINSSTRING ( 'Table'[TextCol], "Sales" ), "Sales", 

    CONTAINSSTRING ( 'Table'[TextCol], "HR" ), "HR", 

    CONTAINSSTRING ( 'Table'[TextCol], "Technology" ), "Technology", 

    CONTAINSSTRING ( 'Table'[TextCol], "Finance" ), "Finance", 

    ""
)

resulting in this

Regards

Phil

[ShajiFiroz]

@PhilipTreacy this worked, awesome 🙂 

[Anonymous]

You can also use the CONTAINSSTRING command in Dax, for example, 

Column =
IF (
    CONTAINSSTRING ( 'Table'[Column1], "Marketing" ),"Marketing",
    IF ( CONTAINSSTRING ( 'Table'[Column1], "Test" ), "Test", 
    "Null" )
)

[ShajiFiroz]

Thanks @Anonymous 

[Anonymous]

Power Query, Conditional column