Latest value from a group/categort

[Akshay167]

Hi,

I need to find the latest workflow value as per the TopicID . This needs to come as a calculated column as shown in the below pic(highlighted in yellow)

[tackytechtom]

Hi @Akshay167,

Here a solution in Power Query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cnYBUkYGRsa6Bpa6BoZKsTqYMoYG+GSMcMoYw2Vc3dw9kGVMcMqY4pQxA8sYAYWigADT1egyCHtAMpFAgGZaLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TopicID = _t, Workflow = _t, Timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TopicID", Int64.Type}, {"Workflow", type text}, {"Timestamp", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TopicID"}, {{"MaxTimestamp", each List.Max([Timestamp]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"TopicID", "MaxTimestamp"}, #"Changed Type", {"TopicID", "Timestamp"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Workflow"}, {"Grouped Rows.Workflow"}),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type", {"TopicID"}, #"Expanded Grouped Rows", {"TopicID"}, "Expanded Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Grouped Rows", {"Grouped Rows.Workflow"}, {"New Workflow"})
in
    #"Expanded Expanded Grouped Rows"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/