Forum Discussion

pmsilvbi's avatar
pmsilvbi
Frequent Visitor
3 years ago
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 purc...
  • ronrsnfld's avatar
    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.