Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Harvey85
Helper I
Helper I

Find the second last value in Power Query

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!!!

2 ACCEPTED SOLUTIONS
_AAndrade
Super User
Super User

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:

_AAndrade_0-1712182251974.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1712188072930.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1712188072930.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
_AAndrade
Super User
Super User

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:

_AAndrade_0-1712182251974.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.