Hi @Dharma,
It seems from your sample your data is structured, if that is the case you can try this.
Just paste the full code below into a new blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DoIwFIVfhXSG0HvbUno3djUOJsYQBmJKYtzU94/AIEJbKi69Hc6X81PXrMjKDDkCS9nFPvsXxv/Z2vtwd+3LsiaN6k6Ptutu1+1Ss5RiRJpwIDABYIqbg8pHORAvCXRy3HvyYJAxpFaYWbAJ4wSClEyq7Zwkwf/x0ySLuR/6uyF+umGwmwgx0G8YyCecfBOmNCncjHGSmjj4Z1zhYJgfzdxPRKoBYeFs/w0dKhcR0uklfsmnSpILK/RxzRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Attendance Punch" = _t, ID = _t, #"Week Type" = _t, Reason = _t]),
GroupAndTransform = Table.Group(Source, {"ID", "Week Type"}, {{"AllRows", each Table.LastN( _, 1), type table [Date=nullable text, Attendance Punch=nullable text, ID=nullable text, Week Type=nullable text, Reason=nullable text]}})[[AllRows]],
ExpandTable = Table.ExpandTableColumn(GroupAndTransform, "AllRows", {"Date", "Attendance Punch", "ID", "Week Type", "Reason"}, {"Date", "Attendance Punch", "ID", "Week Type", "Reason"})
in
ExpandTable
All I’m doing is grouping on ID and Week Type and keeping the last entry from that nested table.
If this doesn’t get you the expected result, you’ll have to add some additional filter logic.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DoIwFIVfhXSG0HvbUno3djUOJsYQBmJKYtzU94/AIEJbKi69Hc6X81PXrMjKDDkCS9nFPvsXxv/Z2vtwd+3LsiaN6k6Ptutu1+1Ss5RiRJpwIDABYIqbg8pHORAvCXRy3HvyYJAxpFaYWbAJ4wSClEyq7Zwkwf/x0ySLuR/6uyF+umGwmwgx0G8YyCecfBOmNCncjHGSmjj4Z1zhYJgfzdxPRKoBYeFs/w0dKhcR0uklfsmnSpILK/RxzRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Attendance Punch" = _t, ID = _t, #"Week Type" = _t, Reason = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each try DateTime.From([Date]) otherwise Date.From([Date])),
GroupAndTransform = Table.Group(#"Added Custom", {"ID", "Week Type"}, {{"AllRows", (IT) => Table.LastN( Table.SelectRows( IT, each [Custom] = List.Max( IT[Custom])), 1), type table [Date=nullable text, Attendance Punch=nullable text, ID=nullable text, Week Type=nullable text, Reason=nullable text, Custom=datetime]}})[[AllRows]],
ExpandTable = Table.ExpandTableColumn(GroupAndTransform, "AllRows", {"Date", "Attendance Punch", "ID", "Week Type", "Reason"}, {"Date", "Attendance Punch", "ID", "Week Type", "Reason"})
in
ExpandTable
.
I hope this is helpful.