Forum Discussion

summer18's avatar
summer18
Helper III
2 years ago
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 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
 
Need Help
  • Rohit11's avatar
    Rohit11
    2 years ago

    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's avatar
    Ahmedx
    2 years ago

    pls code in power query

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

6 Replies

  • Rohit11's avatar
    Rohit11
    Resolver II
    2 years ago

    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's avatar
    Ahmedx
    Super User
    2 years ago

    pls code in power query

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