@jwi1 ,
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg8wUtIBkXqGYEZiZklmXrqCa3FJZm5iSSpQyMhY19BA18jAyACFo2BgYWWAVShWB8VYQ9oYa0CiseZWppaEjTWwpI2xFrQx1px0Yy2IMNaMNsaa0sZYE9KNNSfCWGPaGEtqLiPSWFJzGdBYMyJyGemuNSIiygxJD1sjIgLBkPSUgMPYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stack = _t, position = _t, #"type" = _t, date = _t, time_stamp = _t, most_recent_time_Stamp = _t]),
chgSourceTypes = Table.TransformColumnTypes(Source,{{"stack", type text}, {"position", type text}, {"type", type text}, {"date", type date}, {"time_stamp", type datetime}, {"most_recent_time_Stamp", type datetime}}),
groupWithData = Table.Group(chgSourceTypes, {"stack", "type", "date"}, {{"data", each _, type table [stack=nullable text, position=nullable text, type=nullable text, date=nullable date, time_stamp=nullable datetime, most_recent_time_Stamp=nullable datetime]}, {"calcMostRecent", each List.Max([time_stamp]), type nullable datetime}}),
expandDataCol = Table.ExpandTableColumn(groupWithData, "data", {"position", "time_stamp", "most_recent_time_Stamp"}, {"position", "time_stamp", "most_recent_time_Stamp"})
in
expandDataCol
This will need to be tested on a more diverse dataset as the example data provided only seemed to include one relevant grouping, but it should work fine.
Let me know if you have any trouble applying this solution to your actual data.
Pete
Now accepting Kudos! If my post helped you, why not give it a thumbs-up? Proud to be a Datanaut!
