Help with extracting keywords from a column

[poweruser124]

Hello Power BI Community

I need some help in extracting certain keywords from each cell in a column. I have a column called Tags. It is not a mandatory field to be populated so some cells may be blank. Some cells have only one tag (ex. Project) and some fields have multiple tags separated by a comma (ex. Project, Team- Project management). I want to extract the team names and include them in a slicer so that the entire dataset can be filtered by team name. The format is always Team- Name

Any help with DAX or other means would be helpful.

[quantumudit · Accepted solution]

Hello @poweruser124 

It would have beneficial if you had provided sample data containing all the variations of expected tags. Nevertheless, I assumed certain combinations for the "Tags" column you described and developed a DAX solution accordingly, as you posted in the DAX queries section.

You can create a calculated column with the following DAX :

Category = 
VAR _length = LEN ( [Tags] )
VAR _posHypen = FIND ( "-", [Tags], 1, BLANK () )
VAR _posComma = FIND ( ",", [Tags], 1, BLANK () ) 

// Check if "Team - Category" is present first or, not and select category text
VAR _firstCat =
    IF (
        (
            _posHypen <> BLANK ()
                && _posComma <> BLANK ()
        )
            && _posComma > _posHypen,
        1,
        0
    )
VAR _firstCatText = LEFT ( [Tags], _posComma - 1 )
VAR _category =
    IF (
        _posHypen <> BLANK (),
        IF (
            _firstCat = 1,
            MID ( _firstCatText, _posHypen + 1, 5000 ),
            MID ( [Tags], _posHypen + 1, 5000 )
        )
    )
RETURN
    TRIM ( _category )

Here is the screenshot of the solution where we have successfully extracted the team name from various combinations in the "Tags" column.

I am also attaching the Power BI file for your reference. If this does not resolve your issue, please provide a sample dataset along with additional explanation, so that it would be easier to address the problem you are encountering.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User