How to Dynamically Extract Text Before a Specific Character in Power BI using DAX

In this blog, we will discuss how to dynamically extract text before a specific character, such as a colon (:), in Power BI using DAX.

When working with text data, you may encounter situations where values are separated by a character, like “Data Analyst: Power BI Expert”, and you need to extract only “Data Analyst” dynamically.

Instead of manually splitting the text, DAX functions allow us to automate this process, ensuring scalability and efficiency in reporting. We will walk through a step-by-step guide to implementing this technique in Power BI.

Dataset Name: Title

In the dataset above, as you can see, some job titles contain a colon (:). We want to extract the job title before the colon into a new column. If the colon does not exist, we will extract the entire title.

• To achieve this, we will write a DAX formula. For that, create a New column by following these steps:
• Right-click on the dataset name and select “New column”.
• Enter the following DAX code.

Extracted_Before_Colon = 
VAR ColonPosition =
    FIND ( ":", Title[Job Title],, BLANK () )
RETURN
    IF (
        ISBLANK ( ColonPosition ),
        Title[Job Title],
        LEFT ( Title[Job Title], ColonPosition - 1 )
    )

Explanation of the DAX Code

Find the position of :

• FIND(":", 'Jobs'[Job Title],, BLANK()): If the colon exists, it returns the position; otherwise, it returns BLANK().

Extract the text before :

• If the colon exists, LEFT('Jobs'[Job Title], ColonPosition - 1) extracts text before it.
• If no colon exists, it returns the entire value in the column.

As you can see in the screenshot above, the new calculated column displays the text before the colon (:).

Extract Text after a Specific Character in Power BI using DAX

Extracted_After_Colon =
VAR ColonPosition = FIND(":", Title[Job Title], , BLANK())
RETURN
IF(
ISBLANK(ColonPosition),
Title[Job Title],
RIGHT(Title[Job Title], LEN(Title[Job Title]) - ColonPosition)
)

Explanation of the DAX Code

Find the position of the colon (:)

• FIND(":", Title[Job Title], , BLANK()) returns the position of the : in the text.
• If no : exists, it returns BLANK().

Extract the text after the colon

• RIGHT(Title[Job Title], LEN(Title[Job Title]) - ColonPosition): This extracts the text after the colon by taking the total length and subtracting the position of :.
• If no colon exists, it returns the entire value.

Thanks for reading this post! I hope you found it helpful. Feel free to share it with others or your teammates so they can benefit from it too. 😊