Pick up MAX version number for each item

[DickyMoo]

Hello all,

I have a table showing the OrderHistory for Purchase Orders. Sample attached.

I would like to pull out the DateAuthorised for the latest Version for each OrderGUID, i.e. the yellow highlighted dates. How can I do this in PQ?

I then merge with another table against OrderGUID.

Thanks
Rich


As per the below SQL (I'm still learning SQL so may be a bit rough around the egdes):

SELECT OH1.OrderGUID, OH1.Version, OH1.DateAuthorised
FROM dbrderHistory AS OH1
WHERE OH1.Version =
(SELECT MAX(OH2.Version) as MaxVersion
FROM dbrderHistory AS OH2
WHERE OH2.OrderGUID = OH1.OrderGUID AND OH2.DateAuthorised IS NOT NULL)

 

[Comfy]

Hi DickyMoo,

In PowerQuery you can use the Table.Group function (GroupBy in the Ribbon)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rcyxCsMgEIDhVyk350DP0zOjUTN0yQOIQyGhBNIMkr5/Xbp07vDDP32lgKYweZ41ivIRWRvBkYTROLHJkWPJDgbQPTJ4fx+obf+l7c/9fBy3pa1bgzoUUOSyMxwxcE7IxAaDzYTeRp4nVn5M4SvJPyT6kcJrO9feBbV+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrderGUID = _t, Version = _t, DateAuthorised = _t, Context = _t]),
    #"Grouped Rows" = Table.Group(Source, {"OrderGUID"}, {{"LatestAuthorised", each List.Max([DateAuthorised]), type datetime}})
in
    #"Grouped Rows"

 

[DickyMoo]

Wow! Thank you for your help Comfy, that's exactly what I need.

 

[Comfy]

Wow! Thank you for your help Comfy, that's exactly what I need.

You're very welcome.