Detecting the highest revision code of a document

[Sputnik_17]

Dear all,

First question ever in this Power BI forum.

I have a list of documents references in a table with a numbering sequence that includes the revision number (last two digits of the sequence). It may look like :

.......
CPx03-ASGEA-38-R18122-0001-01

CPx03-ASGEA-38-R18122-0001-02

CPx03-ASGEA-38-R18122-0001-03

.....

What I would like is to insert a new column in which the word "latest rev" would be written beside the highest revision number (rev 03), and if possible, writing "old revision" beside the two others (rev 01 and 02).

I would be very grateful to anyone who could give me the right solution or even give me the keywords to use for the search on this forum.

Many thanks

Best regards.

[AlexisOlson · Accepted solution]

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.

[AlexisOlson]

You 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?

[Sputnik_17]

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 · Accepted solution]

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.

[Sputnik_17 · Accepted solution]

Hi Alexis,

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

[ftgpdx]

Hello, 

It's possible to create a new column using the hyphen delimter. Take a look at this document: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-column-from-example