Forum Discussion
Solved
extract text before delimiter
Hope someone can help me on this whether in Power Query or DAX. My data is Title and ID columns. 1st scenario - I need to create a new column that extracts the data from TITLE field that does ...
Hi summer18
Try below DAX expressions ,
1.
no ":ID" = VAR TitleLength = LEN('Table'[Title])VAR IDLength = LEN('Table'[ID])VAR TitleWithoutID = IF(IDLength>0,SUBSTITUTE('Table'[Title], ":" & 'Table'[ID], ""),'Table'[Title])RETURNTitleWithoutID2.
no ":X" =VAR ColonPosition = FIND(":X", 'Table'[Title], 1, LEN('Table'[Title]))RETURN IF(ColonPosition = LEN('Table'[Title]) - 1,LEFT('Table'[Title], LEN('Table'[Title]) - 2),'Table'[Title])Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Thank you,
pls code in power query
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Hi summer18
Try below DAX expressions ,
1.
no ":ID" = VAR TitleLength = LEN('Table'[Title])
VAR IDLength = LEN('Table'[ID])
VAR TitleWithoutID = IF(IDLength>0,SUBSTITUTE('Table'[Title], ":" & 'Table'[ID], ""),'Table'[Title])
RETURN
TitleWithoutID
2.
no ":X" =
VAR ColonPosition = FIND(":X", 'Table'[Title], 1, LEN('Table'[Title]))
RETURN IF(
ColonPosition = LEN('Table'[Title]) - 1,
LEFT('Table'[Title], LEN('Table'[Title]) - 2),
'Table'[Title]
)
Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Thank you,
