extract text before delimiter

[summer18]

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 not ends with ":X"

                       The column no ":X" is the expected output

2nd scenario - I need to create a new column that extracts the data from TITLE field that does not ends with value corresponding to ID field. Delimiter is ":".    Sample is 2nd row below ID field contains "1234" so I have to remove ":1234" from Title field.

Expected output columns are no ":X" and no ":[ID]"

Title		ID		no ":X"		no ":[ID]"
Alabama:Montgomery:1234		1234		Alabama:Montgomery:1234		Alabama:Montgomery
Alaska:Juneau:X				Alaska:Juneau		Alaska:Juneau:X
Arizona:Phoenix:X				Arizona:Phoenix		Arizona:Phoenix:X
Arkansas:LittleRock:Test:X				Arkansas:LittleRock:Test		Arkansas:LittleRock:Test:X
California:Sacramento:est:3854		3854		California:Sacramento:est:3854		California:Sacramento:est
Colorado:Denver:2021				Colorado:Denver:2021		Colorado:Denver:2021
Connecticut:X:Hartford:AAA				Connecticut:X:Hartford:AAA		Connecticut:X:Hartford:AAA
Delaware:Dover:Type:7877		7877		Delaware:Dover:Type:7877		Delaware:Dover:Type

For scenario 1, this formula works but it also extracts wherever my delimiter can be found.  I only need to extract data if it ENDS with the delimiter

LEFT([Title],SEARCH(":X",[Title],,LEN([Title])+1)-1) 
Eg.  Connecticut:X:Hartford:AAA     my formula does not work here because the output becomes Connecticut  

but it should be Connecticut:X:Hartford:AAA because the delimiter is not found at the end

[Rohit11 · Accepted solution]

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, 

[Ahmedx · Accepted solution]

pls code in power query

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[summer18]

Thank you @Ahmedx for the solution and pbix.  It works!

[Rohit11 · Accepted solution]

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, 

[summer18]

Thank you @Rohit11 .  The DAX works!

[Rohit11]

Glad to know ! 

[Rohit11]

@summer18  
Could you please give a thumbs up and accept this as a solution as well, since you confirmed that DAX is working too?