How do I return the text after the second underscore?

[Anonymous]

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!

[hmericchan]

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(). 😅

[Anonymous]

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 · Accepted solution]

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