Search Incident number

 To create a new column in Power BI that extracts a word starting with "INC" followed by a 10-digit number from the RTSK Worknote column, you can use DAX with the SEARCH, MID, and IF functions combined with a regular expression-like pattern. Here's how you can do it:

Steps to Create the New Column

1. Open Power BI Desktop.

2. Go to the Modeling tab.

3. Click on New Column.

4. Enter the following DAX formula:

dax

ExtractedINC = 
VAR TextToSearch = 'RTSK Data'[RTSK Worknote]
VAR StartPos = SEARCH("INC", TextToSearch, 1, LEN(TextToSearch))
VAR ExtractedText = IF(
    StartPos > 0,
    MID(TextToSearch, StartPos, 13),  -- "INC" + 10 digits = 13 characters
    BLANK()
)
VAR IsValidINC = IF(
    AND(
        LEN(ExtractedText) = 13,
        VALUE(MID(ExtractedText, 4, 10)) >= 0
    ),
    ExtractedText,
    BLANK()
)
RETURN
    IsValidINC

Explanation:

• SEARCH("INC", TextToSearch, 1, LEN(TextToSearch)): Finds the starting position of the string "INC" in the RTSK Worknote column.

• MID(TextToSearch, StartPos, 13): Extracts 13 characters starting from the position where "INC" is found (3 for "INC" and 10 for the digits).

• IF(StartPos > 0, ..., BLANK()): Ensures that if "INC" is not found, the result is blank.

• AND(LEN(ExtractedText) = 13, VALUE(MID(ExtractedText, 4, 10)) >= 0): Checks if the extracted text has exactly 13 characters and if the last 10 characters are digits.

• IsValidINC: Assigns the extracted value if it's valid; otherwise, it returns blank.

Using the New Column

This new column, ExtractedINC, will contain the word starting with "INC" followed by a 10-digit number if it exists in the RTSK Worknote column. If no such word is found, it will be blank.