Find value of a column based on the maximum value of another

[Terrassa]

I have a table with purchase orders. Each order has several items, and each item has several sequences (every time an item is modified it generates a new sequence). There's a column with the status of each sequence.

In Power Query I need to find the status of the latest sequence of each item of an order.

This is an example of the table:

Order	Item	Sequence	Status
1000	1	0	A
1000	1	1	B
1000	1	2	C
1000	2	0	C
1000	2	1	B
1000	3	0	B
1000	3	1	C
1001	1	0	A
1001	1	1	B
1001	2	0	B

This is what I need as a result:

Order	Item	Sequence	Status
1000	1	2	C
1000	2	1	B
1000	3	1	C
1001	1	1	B
1001	2	0	B

The result should be the max sequence for each order/item and its status.

Thank you very much for any help that you could provide.

[Vijay_A_Verma · Accepted solution]

Insert this step where Source must be replaced by your previous step name

Table.Group(Source, {"Order", "Item"}, {{"Sequence", each List.Max([Sequence])}, {"Status", each Table.Max(_,"Sequence")[Status]}})

A sample code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIEYhDtqBSrgyIIwk7ogkZA7IwsaATVjiGIod0YqhJD0BBZuyE2Jxlic5Ihku1AwVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Item = _t, Sequence = _t, Status = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Order", "Item"}, {{"Sequence", each List.Max([Sequence])}, {"Status", each Table.Max(_,"Sequence")[Status]}})
in
    #"Grouped Rows"

[Vijay_A_Verma · Accepted solution]

Insert this step where Source must be replaced by your previous step name

Table.Group(Source, {"Order", "Item"}, {{"Sequence", each List.Max([Sequence])}, {"Status", each Table.Max(_,"Sequence")[Status]}})

A sample code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIEYhDtqBSrgyIIwk7ogkZA7IwsaATVjiGIod0YqhJD0BBZuyE2Jxlic5Ihku1AwVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Item = _t, Sequence = _t, Status = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Order", "Item"}, {{"Sequence", each List.Max([Sequence])}, {"Status", each Table.Max(_,"Sequence")[Status]}})
in
    #"Grouped Rows"

[Terrassa · Accepted solution]

Thank you very much for your quick answer, it worked perfectly.

I understand how the List.Max for the sequence works, but I don't understand the Table.Max for the status. Could you please explain what this command is doing?

[Vijay_A_Verma]

Table.Max(_,"Sequence") - This takes the maximum of Sequence field.