cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Super User

Hi @Harvey85 ,

``````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:

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

Proud to be a 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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
2 REPLIES 2
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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi @Harvey85 ,

``````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:

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

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors