Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!!!
Solved! Go to 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:
Proud to be a 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.
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.
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:
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
80 | |
79 | |
58 | |
36 | |
35 |
User | Count |
---|---|
99 | |
56 | |
56 | |
46 | |
40 |