Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How do I return the text after the second underscore?

Hello!

 

I have a column with text like:

QWER_SDFGWE_QERGR

QWERG_WER_WEGR

 

How do I only keep the text after the second underscore?

What is the DAX, Power Query and EXCEL code to do that?

 

Thanks!

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    The Power Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MY0PdnFzD3eNNwRylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", type text}, {"Column1.3", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.1", "Column1.2"})
in
    #"Removed Columns"

     

    The DAX:

    Column = 
    RIGHT(
        [Column1],
        LEN([Column1]) - 
            SEARCH(
                "_",
                [Column1],
                SEARCH("_",[Column1],1) + 1
            )
    )

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi

     

    Select the column you want to extract the data from .

    Go to the transform tab in the ribbon and click on extract.

    Select Text after delimiter from dropdown

    A new dialog box will appear. 

    Write down the delimiter and mention the delimiter position from advanced options.

     

    You are good to go.

     

    Thanks,

    Uday

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    The Power Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MY0PdnFzD3eNNwRylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", type text}, {"Column1.3", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.1", "Column1.2"})
in
    #"Removed Columns"

     

    The DAX:

    Column = 
    RIGHT(
        [Column1],
        LEN([Column1]) - 
            SEARCH(
                "_",
                [Column1],
                SEARCH("_",[Column1],1) + 1
            )
    )

     

  • hmericchan's avatar
    hmericchan
    Frequent Visitor
    1 year ago

    I would use Text.AfterDelimiter() twice to get the right most delimiter, if that's always the last one. I like to directly type the formula in m instead of using the split column by delimiter as it can combine multiple steps, such as have Text.Clean() or Text.Trim(). ğŸ˜…