Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.