Forum Discussion
Power Query - Finding most recent Date for a group
Hello Everyone,
My data contains historical updates made to part numbers at a given location. I would like to filter using Power Query to only the most recent record for each location's part number. I have many values in GrpParent_Loc_KPN - I want to find the most recent Rebuild_DT.
I have a Rebuild_DT column which is a datetime field. I also have a GrpParent_Loc_KPN which is a concatenation of three fields. I would like to identify with a 1 or 0 whether or not the Rebuild_DT is the most recent for each GrpParent_Loc_KPN.
Hello Matthew_Theis
this solution uses the same approach as the one from Anonymous, but it's more compact
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR2lxESlWB2ImBEWMbi6pCRMdUhixjCx5GSImBGSXpBYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rebuild_DT = _t, GrpParent_Loc_K = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rebuild_DT", type date, "en-US"}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"GrpParent_Loc_K"}, {{"MaxDate", each List.Max([Rebuild_DT]), type date}}) in #"Grouped Rows"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
2 Replies
Hello Matthew_Theis
this solution uses the same approach as the one from Anonymous, but it's more compact
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR2lxESlWB2ImBEWMbi6pCRMdUhixjCx5GSImBGSXpBYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rebuild_DT = _t, GrpParent_Loc_K = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rebuild_DT", type date, "en-US"}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"GrpParent_Loc_K"}, {{"MaxDate", each List.Max([Rebuild_DT]), type date}}) in #"Grouped Rows"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyPaste it in Advanced editor with a blank query, next time please provide some data in a format we can copy:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNKs3MSYl3CVHSUXIvKghILErNK4n3yU+O91aK1YlWMjDUByIjAyNDoILizNRcuKgRVlG42vScFEylyILGGIJG6NpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rebuild_DT", type datetime}, {"GrpParent_Loc_K", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"GrpParent_Loc_K"}, {{"allrows", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [a=Table.AddIndexColumn([allrows],"Index",0,1),
b=Table.AddColumn(a,"new",each if [Rebuild_DT]=List.Max(a[Rebuild_DT]) then 1 else 0)][b]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Rebuild_DT", "GrpParent_Loc_K", "Index", "new"}, {"Rebuild_DT", "GrpParent_Loc_K", "Index", "new"})
in
#"Expanded Custom"
