Forum Discussion
Day with latest time
Hello,
I have this table. I want to have items with date and latest time. See example.
Any idea how to do it?
| Item | Date | Amount |
| A | 1.3.2023 15:03 | 1 |
| A | 1.3.2023 9:31 | 8 |
| A | 1.3.2023 6:00 | 4 |
| A | 25.2.2023 7:55 | 5 |
| A | 25.2.2023 6:04 | 3 |
| B | 15.2.2023 16:11 | 7 |
| B | 15.2.2023 11:00 | 3 |
| B | 15.2.2023 15:00 | 1 |
| C | 9.2.2023 12:23 | 5 |
| C | 9.2.2023 9:56 | 3 |
=>
| Item | Date | Amount |
| A | 1.3.2023 15:03 | 1 |
| A | 25.2.2023 7:55 | 5 |
| B | 15.2.2023 16:11 | 7 |
| C | 9.2.2023 12:23 | 5 |
2 Replies
NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Item",{"n",each Table.Sort(_,{"Date",1}){0}})[n])
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7NDcAgCAbQVRrPxgiIVm5txzDuv0axJCZtvcH3wk9r7nDeQaCAEWkDlkgjcN1/qAqB9vtfssSofZqCHNCoCLMGvCCdShrQQ+fYNwmywLhVVgZ2bTnHZvb9pVWdhII0P3lRFc62sN8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type datetime}, {"Amount", Int64.Type}}), #"Added Dt" = Table.AddColumn(#"Changed Type", "Dt", each Date.From([Date])), Grouped = Table.Group(#"Added Dt", "Dt", {"Grp", each Table.Max(_, "Date")}, 0), #"Expanded Grp" = Table.RemoveColumns(Table.ExpandRecordColumn(Grouped, "Grp", {"Item", "Date", "Amount"}), "Dt") in #"Expanded Grp"
