Forum Discussion
pmsilvbi
3 years agoFrequent Visitor
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 purc...
- 3 years ago
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.
ronrsnfld
Super User
3 years agoUse 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
3 years agoFrequent Visitor
Thanks so much for your suggestion. I'll do some further research on how to apply this.