Find the second last value in Power Query

[Harvey85]

Hello,

I have this table:

Key		Date		Color
1		3/3/2020		Red
1		3/5/2021		Blue
1		3/8/2022		Orange
2		1/1/2020		White
2		1/3/2022		Yellow
3		2/1/2021		Grey

I need to get the second last date value for each Key using Power Query (if there's only one value for that Key just bring that one).

Expected Output:

Key		Date		Color
1		3/5/2021		Blue
2		1/1/2020		White
3		2/1/2021		Grey

Do you know how could achieve this on an simple/easy way?

Thank you!!!

[_AAndrade · Accepted solution]

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:

[Ashish_Mathur · Accepted solution]

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.

[_AAndrade · Accepted solution]

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: