Pick up MAX version number for each item

DickyMoo

New member
Joined
Mar 7, 2016
Messages
27
Reaction score
0
Points
0
Location
London
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 dbo_OrderHistory AS OH1
WHERE OH1.Version =
(SELECT MAX(OH2.Version) as MaxVersion
FROM dbo_OrderHistory AS OH2
WHERE OH2.OrderGUID = OH1.OrderGUID AND OH2.DateAuthorised IS NOT NULL)
 

Attachments

  • MAX version number.xlsx
    8 KB · Views: 9
Hi DickyMoo,

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

Code:
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"
 
Wow! Thank you for your help Comfy, that's exactly what I need.
 
Back
Top