Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
summer18
Helper III
Helper III

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
 
2 ACCEPTED SOLUTIONS
Rohit11
Resolver I
Resolver I

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]
    )

 

 

Rohit11_0-1711450326138.png

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

 

Thank you, 

View solution in original post

Ahmedx
Super User
Super User

pls code in power query

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

Screenshot_1.pngScreenshot_2.png

View solution in original post

6 REPLIES 6
Ahmedx
Super User
Super User

pls code in power query

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

Screenshot_1.pngScreenshot_2.png

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

Rohit11
Resolver I
Resolver I

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]
    )

 

 

Rohit11_0-1711450326138.png

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

 

Thank you, 

Thank you @Rohit11 .  The DAX works!

Glad to know ! 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.