Finding the penultimate date from a list

[pmsilvbi]

Hi All

I have a table containing all stock codes and the dates they were purchased. One Stock code may have been purchased multiple times.

I am trying to show the last time each stock code was purcahsed, the qty ordered and the price paid as well as the previous time it was purcahsed, qty and the price paid then.

I have managed to extract the last purchase date by grouping the stock codes then using list.max to extract the required information. But do I need to maybe create an index on the original data to lookup the required information? I have some 170,000 rows

Supplier	StockCode	PoDate	PurchaseOrder	PurchaseOrderLin	LineDueDate	DateReceived	QtyReceived	PriceReceived
EDEN	T/A014/003W	05/06/2023	000000000078577	1	12/06/2023	28/06/2023	6	49.9
EDEN	T/A014/003W	30/03/2022	000000000075200	1	15/04/2022	29/04/2022	12	49.9
EDEN	T/A014/003W	01/03/2021	000000000071790	1	12/03/2021	23/03/2021	6	35
EDEN	T/A014/003W	19/11/2020	000000000070866	3	30/11/2020	09/12/2020	6.162	35
EDEN	T/A014/003W	31/10/2019	000000000067684	1	08/11/2019	25/11/2019	6	35
EDEN	T/A014/003W	12/12/2017	000000000061481	1	15/12/2017	07/02/2018	12	27
EDEN	T/A014/003W	18/03/2016	000000000055865	1	24/03/2016	12/04/2016	12	27
EDEN	T/A014/003W	18/03/2016	000000000055865	1	24/03/2016	19/04/2016	1	27
EDEN	T/A014/003W	10/07/2014	000000000050078	1	18/07/2014	23/07/2014	13.86	54
EDEN	T/A014/003W	06/11/2012	000000000044619	1	22/11/2012	27/11/2012	13.384	27.2
EDEN	T/A014/003W	02/06/2011	000000000040319	1	17/06/2011	23/08/2011	12	27.2
EDEN	T/A014/003W	22/10/2009	000000000035762	1	30/10/2009	09/11/2009	12.008	27.2
EDEN	T/A014/003W	23/07/2004	000000000016338	1	30/07/2004	05/08/2004	6.484	21.57
EDEN	T/A014/003W	16/01/2003	000000000010282	1	24/01/2003	07/02/2003	6	16.5
EDEN	T/A014/003W	21/05/2002	000000000008129	1	23/05/2002	23/05/2002	6.57	16.5
EDEN	T/A014/003W	07/11/2001	000000000006491	6	09/11/2001	08/11/2001	6.988	16.5
EDEN	T/A014/003W	10/01/2001	000000000003761	1	12/01/2001	15/01/2001	6	17.45

Any assistance would be greatly apprecaited

[ronrsnfld · Accepted solution]

Use List.MaxN(Your_List,2){1}

However, if you are returning multiple columns in a "group", you might be better served using Table.MaxN

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVQxbsMwDPxL5kAiKYmixgLN2qlAhyD//0ZFWbJJoLGXGvBwxvmOIk98Pm+Pz8fX7X77jh+AOQKkn46gROBIQEnB/lQptfYvqC8ZCokB3N/cQru97m/kE0RISicvXwhgyfcK8qJQMwDpUh9w6qPXx9rAlL9TKBmg5afyXhxbRFQyeHEQHr9u5zOUzqcFOCDThUHCiNB/wOYMuLLkWT3IZjAoVAy4rJ62arB6ccyCR+sNpUYYQFbrqZ6oy9ZIZKdeinCZ6pQNRaeQD/Df6s2qX4n3TFYlZy+uqV+NEUPRzOwAUxA1KfkklDzH5EOfM4/BjfrIUKga0A3SmD7VQCce81KiD36GtHtgNRQ9hCwwXc/0tUCNJvhoplJHqnFm/6DMuzIAUgCQa4/ZWPCDQE5JDg9DKdsZBuCQtzZhKGfD5gijLr/fEEjWOTRJB2XeAlj7DTmcXDLq+6co3Y8aBGkfdTIUB1grv3KAGQ7wgwbObe2wvfd2YQzAoYlcWuiF+MsiVd43BRmKbuzDYiQt5K7/+gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, StockCode = _t, PoDate = _t, PurchaseOrder = _t, PurchaseOrderLin = _t, LineDueDate = _t, DateReceived = _t, QtyReceived = _t, PriceReceived = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Supplier", type text}, {"StockCode", type text}, {"PoDate", type date}, {"PurchaseOrder", Int64.Type}, 
        {"PurchaseOrderLin", Int64.Type}, {"LineDueDate", type date}, {"DateReceived", type date}, 
        {"QtyReceived", type number}, {"PriceReceived", type number}},"en-150"),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"StockCode"}, {
        {"all", (t)=>Table.MaxN(t, each [PoDate],2), 
        type table [Supplier=nullable text, StockCode=nullable text, PoDate=nullable date, PurchaseOrder=nullable number, PurchaseOrderLin=nullable number, LineDueDate=nullable date, DateReceived=nullable date, QtyReceived=nullable number, PriceReceived=nullable number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"StockCode"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", 
        {"Supplier", "StockCode", "PoDate", "PurchaseOrder", "PurchaseOrderLin", "LineDueDate", "DateReceived", "QtyReceived", "PriceReceived"})
in
    #"Expanded all"

In the #"Expanded All" step you can deselect the columns you might not want to show.

[pmsilvbi]

Thanks so much for your suggestion. I'll do some further research on how to apply this.