Dear Community,
I'm asking for your kind help for an M-code soulution for the following:
I'd like to count two type of events occuring in periods. Putting it to tables:
input table 1. periods
| period id | start of period |
| 1 | 2024-12-01 10:50:42 |
2 | 2024-12-02 02:11:10 |
| 3 | 2024-12-02 08:11:10 |
| 4 | 2024-12-03 08:11:10 |
| 5 | 2024-12-05 15:50:00 |
input table 2. events
| event type | timestamp |
| a | 2024-12-01 10:55:30 |
| b | 2024-12-02 09:20:14 |
| a | 2024-12-02 09:21:19 |
| a | 2024-12-02 19:45:53 |
| a | 2024-12-02 21:11:56 |
| a | 2024-12-03 09:00:00 |
| b | 2024-12-03 09:00:02 |
| b | 2024-12-04 18:37:55 |
| b | 2024-12-05 16:13:14 |
expected outcome:
| period id | start of period | event type a | event type b |
| 1 | 2024-12-01 10:50:42 | 1 | 0 |
2 | 2024-12-02 02:11:10 | 0 | 0 |
| 3 | 2024-12-02 08:11:10 | 3 | 1 |
| 4 | 2024-12-03 08:11:10 | 1 | 2 |
| 5 | 2024-12-05 15:50:00 | 0 | 1 |
First idea was to create end times from input table 1 start of period data - to have start and end stamps. I succeeded it by creating an index column and a list with List.Range(#"Added Index"[start of period], [Index]+1,1) to have
| period id | start of period | start of next period |
| 1 | 2024-12-01 10:50:42 | 2024-12-02 02:11:10 |
2 | 2024-12-02 02:11:10 | 2024-12-02 08:11:10 |
| 3 | 2024-12-02 08:11:10 | 2024-12-03 08:11:10 |
| 4 | 2024-12-03 08:11:10 | 2024-12-05 15:50:00 |
| 5 | 2024-12-05 15:50:00 | |
but now I'm lost how to search and list timestamps of events in the periods.
Any help would be much appreciated. Thanks in advance.
