Detecting the highest revision code of a document

You can group by Filename, taking the max over Revision and then merge that back with the pre-grouped query to calculated the MaxRevision for each Filename. Then write a custom column to check if Revision = MaxRevision.

Here's a sample query you can paste into the Advanced Editor and then examine the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dczBDYAwDAPAXfIuDztVBGvwrbL/GhShFoPglcgnuzXbsXJbYOX6+oVl+cr5k/vIQQ7ory6dQpW5hcDsBB6duNeCb6EKVVzFVapKtcwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Complete Filename" = _t, Filename = _t, Revision = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Complete Filename", type text}, {"Filename", type text}, {"Revision", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Filename"}, {{"MaxRevision", each List.Max([Revision]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Filename"}, #"Grouped Rows", {"Filename"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxRevision"}, {"MaxRevision"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Revision status", each if [Revision] = [MaxRevision] then "Latest rev" else "Old rev", type text)
in
    #"Added Custom"

This self-merge process is exactly what I describe in Approach #4 in my recent blog post. Take a look at that for a bit more explanation on how to generate this step yourself.

Hi Alexis,

Many thanks for your help. It works and helps me a lot !!