Último valor de un grupo/categoría

[Syndicate_Admin]

Hola

Necesito encontrar el valor de flujo de trabajo más reciente según TopicID . Esto debe venir como una columna calculada como se muestra en la imagen de abajo (resaltada en amarillo)

[Syndicate_Admin]

@Akshay167,

Aquí una solución en Power Query:

Aquí el código M:

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"

Hazme saber si esto ayuda 🙂

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