Find the second last value in Power Query

Hi Harvey85 ,

Please try this script and see if this solve your problem:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLWN9Y3MjAyADKDUlOUYnVgwqYgYRDTKac0FUncAiRuBGT6FyXmpUNkQFxDfUOYQeEZmSXIEsYwLZGpOTn55WAZYyDXCKIFZK57UWqlUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Date = _t, Color = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Date", type date}, {"Color", type text}}),
     #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {
        {"SecondLastDate", each 
            let
                SortedDates = List.Sort([Date], Order.Descending),
                SecondLastDate = if List.Count(SortedDates) > 1 then SortedDates{1} else List.First(SortedDates)
            in
                SecondLastDate, type date}
    }),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Key"},#"Grouped Rows",{"Key"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"SecondLastDate"}, {"SecondLastDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "RemoveColumn", each if [Date] = [SecondLastDate] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([RemoveColumn] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SecondLastDate", "RemoveColumn"})
in
    #"Removed Columns"

Final output:

 

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1gUiIwMjAyAnKDVFKVYHIWEKkgBxnHJKU1FkLEAyRkCOf1FiXjpEDsQ1MNQFIqhx4RmZJahSxjBtkak5OfnlYDljkJwRVBvIfPei1Eql2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Date = _t, Color = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each Table.Min(Table.MaxN(_,"Date",2),"Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Color"}, {"Date", "Color"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Date", type date}, {"Color", type text}})
in
    #"Changed Type1"

Hope this helps.