Forum Discussion
Extract text from cell based on delimiter
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
Hi Anonymous
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.
7 Replies
ajohnso2Solution Supplier
- ajohnso2
in that case try this, if the length of MPIP, SPIP changes in the future you will need to re evaluate this logic.
Hi Anonymous
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.