Power M Query/Kusto take first from group

Question

I have a table which looks like this:

id  timestamp  value1  value2
 1  09:12:37     1       1
 1  09:12:42     1       2
 1  09:12:41     1       3
 1  10:52:16     2       4
 1  10:52:18     2       5
 2  09:33:12     3       1
 2  09:33:15     3       2
 2  09:33:13     3       3

I need to group by id and value1. For each group i want to have the row with the highest timestamp.

The result for the table above would look like this:

id  timestamp  value1  value2
 1  09:12:42     1       2
 2  09:33:15     3       2

I know there is the summarize operator which would give me this:

mytable
| project id, timestamp, value1, value2
| summarize max(timestamp) by id, value1

Result:
     id  timestamp  value1
      1  09:12:42     1
      2  09:33:15     3

But i was not able to get value2 for this rows too.

Thanks in advance

Answer 1

If i understand your question correctly, you should be able to use summarize arg_max():

doc: https://docs.microsoft.com/en-us/azure/kusto/query/arg-max-aggfunction

datatable(id:long, timestamp:datetime, value1:long, value2:long)
[
 1, datetime(2019-03-20 09:12:37), 1, 1,
 1, datetime(2019-03-20 09:12:42), 1, 2,
 1, datetime(2019-03-20 09:12:41), 1, 3,
 1, datetime(2019-03-20 10:52:16), 2, 4,
 1, datetime(2019-03-20 10:52:18), 2, 5, // this has the latest timestamp for id == 1
 2, datetime(2019-03-20 09:33:12), 3, 1,
 2, datetime(2019-03-20 09:33:15), 3, 2, // this has the latest timestamp for id == 2
 2, datetime(2019-03-20 09:33:13), 3, 3,
]
| summarize arg_max(timestamp, *) by id

This will result with:

| id | timestamp                   | value1 | value2 |
|----|-----------------------------|--------|--------|
| 2  | 2019-03-20 09:33:15.0000000 | 3      | 2      |
| 1  | 2019-03-20 10:52:18.0000000 | 2      | 5      |