Extract text from cell based on delimiter

[sanjayarun55]

Hi All,

I am using direct query in my power BI dashboard. what i am trying to do is create a column based in text extracted from another column called group.

Group: 

AVL_MPIP_R(0,5)

ALV_1_MPIP_R(0,5)

AVL_SPIP_R(0,7)

AVL_1_SPIP_R(0,95) and so on

group is always (3chars)_(4chars)_(remaining chars) or  (3chars)_(1or2)_(4chars)_(remaining chars)

1) Need the text after the last demiliter as a separate column 
2) need the text (MPIP) after AVL_ or sometimes AVL_1 in a new column. MPIP is always of same length. the only change is the for some cells the _1 comes and for some it doesnt. 

please note i am using direct query and pretty new to power bi, so ideally would like it as a step in transform data. if thats not possible maybe as a calculated column in my table

[Anonymous · Accepted solution]

Hi @sanjayarun55 

Please try the following calculate column:

ExtractedText = 
VAR FullString = 'Table'[Group]
VAR StartPosition = 
    IF(
        MID(FullString, 5, 1) = "1",
        7,  
        5   
    )
RETURN MID(FullString, StartPosition, LEN(FullString) - StartPosition + 1)

 Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanjayarun55]

AMAZINGGG. just what i wanted thanks appreciate a lot

[ajohnso2]

[sanjayarun55]

sorry MPIP is not a constant name just the size is always 4 i have updated my original post

[ajohnso2]

in that case try this, if the length of MPIP, SPIP changes in the future you will need to re evaluate this logic.

[sanjayarun55]

Thanks for the effort but doesnt look like this works on direct query. POwer BI is asking me to convert to all tables to import mode

[ajohnso2]

Do you have access to the database your pulling your data from? You can either do the equivalent there or if you are passing SQL as part of your direct query you can format your column there.