Forum Discussion
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 !!
Super UserYou probably want to split it into name and version and then mark the maximal version for each distinct name.
This is possible in DAX or the Query Editor. Which type of solution are you looking for?
Hi Alexis,
Many thanks for your reply.
Indeed, I will split filename and revision from the source filename using the hyphen delimiter.
AT the end, I wish my table would provide the revision status like shown in the table below :
Please note the table formatting is not a point for this request, only data is important.
Many thanks for your help and advise.
Best regards
- AlexisOlson
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 !!