Get text from last delimiter in string

[swolfe2]

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 previously been using Text.BetweenDelimiters, and it has been working great. However, business users are now asking to retrieve the last hashtagged word rather than the first. The yellow highlighted parts of this are what I would want to be extracting into a separate column. The Text.BetweenDelimiters is extracting the first instance.

Here's example code that can be placed in Advanced Editor.

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]),
    #"Added Custom" = Table.AddColumn(Source, "Extract", each if Text.Contains([Sample Strings],"#") then Text.BetweenDelimiters([Sample Strings], "#", " ") as text else [Sample Strings]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Delimiter List Space", each List.RemoveMatchingItems(Text.Split([Sample Strings]," "),{""})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Word Count", each List.Count([Delimiter List Space])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "# Count", each List.Count( Text.ToList(Text.Select([Sample Strings], "#"))))
in
    #"Added Custom3"

Any help/guidance would be appreciated!

[swolfe2 · Accepted solution]

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"