Forum Discussion

ConnorR's avatar
ConnorR
New Member
4 years ago

Sorting Data with the most recent timestamp

I am attempting to write a power query formula that will put a flag in the a column based on the most recent date for each of the multiple criteria that I have. Could someone please help push me a direction to solving this problem as I have not been able to for weeks.

 

Thanks

 

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    ConnorR So, try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9LVdJRCinPB5EZRakgnqG+ob6RgZGRgpmVgQEQKTj6KsXqEFBrjqoWJg9RDdGJy1y8apHNjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Criteria1 = _t, Criteria2 = _t, Criteria3 = _t, Timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Criteria1", type text}, {"Criteria2", type text}, {"Criteria3", type text}, {"Timestamp", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Criteria1", "Criteria2", "Criteria3"}, {{"Timestamp", each List.Max([Timestamp]), type nullable datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Criteria1] = [Criteria1] and [Criteria2] = [Criteria2] and [Criteria3] = [Criteria3])),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Timestamp] = #"Filtered Rows"{0}[Timestamp] then 1 else null)
in
    #"Added Custom"