Forum Discussion

swolfe2's avatar
swolfe2
Helper I
4 years ago
Solved

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...
  • swolfe2's avatar
    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"