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

     

     

Anonymous's avatar
Anonymous
Not applicable
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