Forum Discussion
Solved
Finding the penultimate date from a list
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
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.
2 Replies
ronrsnfldSuper User
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.
Thanks so much for your suggestion. I'll do some further research on how to apply this.