Forum Discussion

StevieBeales's avatar
StevieBeales
Regular Visitor
1 year ago
Solved

Identify and Note latest version

Hi Forum, I can see some similar challenges from members, but not quite working for me. I have [an extract of] a list below, and where there are duplicates, there is an additional attribute which gi...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi StevieBeales ,
    Sample data
    IDReference

    IDReference

    15291150 2000-87144-MP-3323-0001
    14968792 2000-87144-MS-6999-0001
    14968793 2000-87144-MS-7303-0001
    15038296 2000-87144-MS-7303-0001
    15038798 2000-87144-MS-7303-0001
    15088876 2000-87144-MS-7303-0001


    You can try the following code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcxLCoAwDEXRvWTcQD5tk7cIQXBYuv9t2KEoqNPL4Y5B2gyqTaiQiQhnaK287exuzqsozbJYRc+A3djBHcCD+YOFy/XWxNPQ/7BAfrPMjNfbPAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Reference = _t]),
    GroupedRows = Table.Group(Source, {"Reference"}, {{"MaxID", each List.Max([ID]), type number}}),
    MergedTables = Table.NestedJoin(Source, {"Reference"}, GroupedRows, {"Reference"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "GroupedRows", {"MaxID"}),
    AddedCustom = Table.AddColumn(ExpandedTable, "Label", each if [ID] = [MaxID] then "Latest Version" else null),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"MaxID"})
in
    RemovedColumns

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

How about this:

 

The expression in text for easy copy paste:

let
   allversions = Table.SelectRows(Source, (T) => T[#"Attribute:REFERENCE"] = [#"Attribute:REFERENCE"]),
   max_version = List.Max(allversions[#"Attribute:VERSION_LABEL"]),
   is_latest = max_version = [#"Attribute:VERSION_LABEL"]
in
   is_latest