Forum Discussion
swolfe2
4 years agoHelper I
Get text from last delimiter in string
I have a column of text strings that can have a hashtag "#" at any point in the string, or have none at all. Also, there's the ability to have multiple hashtags in a single string. I have previou...
- 4 years ago
I was able to solve the ask with the M below, for anyone coming in the future
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcw9CsAgDIDRqwSz9kbioDRiIFapkfb4/ZHi0iUk74NYa4L4BKiJG7zrM4xbrMGjdFknzjIJA/3xaH4bf+j0uQq9R4nDIu9NAXMX5a+hcqYGWKncBCi+qXHuAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample Strings" = _t]), //This gets the FIRST occurrence of each hashtag if exists, otherwise just the Sample Strings value #"Original Extract" = Table.AddColumn(Source, "Extract", each if Text.Contains([Sample Strings],"#") then Text.BetweenDelimiters([Sample Strings], "#", " ") as text else [Sample Strings]), //You wouldn't actually need this. It's a visual representation of the variable used in the final Last Word output #"Count Hashtags" = Table.AddColumn(#"Original Extract", "# Count", each List.Count( Text.ToList(Text.Select([Sample Strings], "#")))), //We're going to declare a variable that will do a few different things. If 0, just Simple Strings; if 1, then use the single hashtag; otherwise, get the string from the last hashtag #"Final Output" = Table.AddColumn(#"Count Hashtags", "Last Word", each let _HashCount = List.Count( Text.ToList(Text.Select([Sample Strings], "#"))) in Text.Trim( if _HashCount = 0 then [Sample Strings] else if _HashCount = 1 then Text.BetweenDelimiters([Sample Strings], "#", " ") else Text.Replace(List.LastN(Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)([Sample Strings]),1){0},"#","") ) ) in #"Final Output"
swolfe2
4 years agoHelper I
I was able to solve the ask with the M below, for anyone coming in the future
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcw9CsAgDIDRqwSz9kbioDRiIFapkfb4/ZHi0iUk74NYa4L4BKiJG7zrM4xbrMGjdFknzjIJA/3xaH4bf+j0uQq9R4nDIu9NAXMX5a+hcqYGWKncBCi+qXHuAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample Strings" = _t]),
//This gets the FIRST occurrence of each hashtag if exists, otherwise just the Sample Strings value
#"Original Extract" = Table.AddColumn(Source, "Extract", each if Text.Contains([Sample Strings],"#") then Text.BetweenDelimiters([Sample Strings], "#", " ") as text else [Sample Strings]),
//You wouldn't actually need this. It's a visual representation of the variable used in the final Last Word output
#"Count Hashtags" = Table.AddColumn(#"Original Extract", "# Count", each List.Count( Text.ToList(Text.Select([Sample Strings], "#")))),
//We're going to declare a variable that will do a few different things. If 0, just Simple Strings; if 1, then use the single hashtag; otherwise, get the string from the last hashtag
#"Final Output" = Table.AddColumn(#"Count Hashtags", "Last Word", each let _HashCount = List.Count( Text.ToList(Text.Select([Sample Strings], "#"))) in
Text.Trim(
if _HashCount = 0 then [Sample Strings]
else if _HashCount = 1 then Text.BetweenDelimiters([Sample Strings], "#", " ")
else Text.Replace(List.LastN(Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)([Sample Strings]),1){0},"#","")
)
)
in
#"Final Output"