Forum Discussion
Anonymous
6 years agoNot applicable
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? ...
- 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 ) )
Greg_Deckler
Community Champion
6 years agoThe 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
)
)