Forum Discussion

troyphillips's avatar
troyphillips
Frequent Visitor
3 years ago

Split column by delimeter and return column with latest date

Hello,   I have a user submitted text field in SharePoint, called Notes, in my dataset, and I am looking to always return the latest note in any row. The Notes field is always split by a semicolon ...
Need Help
jbwtp's avatar
jbwtp
Memorable Member
3 years ago

Hi troyphillips, This is doable (depending on the formatting of the Notes field). In the most simplist way it can be done like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY07DoJAFEW3cjO1ho8WJlRGLYixETokZmAeZOLwhsBA4m5ciyuTSLSnu8U552aZuNJIPBCKJw5D72xDnVgJf+f5gRf64QZr7JUihTg9Xe7xEc5C82h1Sahk6eBkYSi68aSEfyXRNcNWFSQrjNJoJZ22jKqzDYaeumg+WWCIfJWJ1LYIfMSOmh6JNWrOBNtf5qzLxzfTGsmsuX6/0BC5aUULWJHnHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Name" = _t, Notes = _t, #"Latest Note" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Name", type text}, {"Notes", type text}, {"Latest Note", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Automated", each List.Last(Text.Split([Notes], "
")))
in
    #"Added Custom"

Cheers,

JB