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

     

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(). ðŸ˜…