Forum Discussion
Count rows if between a certain time period in PowerQuery
Dear all,
For below earlier asked question I've received the following DAX expression, which I'm very grateful for:
Previous post:
I have the following issue which I think should be relatively easy to solve, but I have not found a solution yet. Currently I have below table in excel but I would like to get the same result (Column F) in a query in PowerBI. Basically it comes down to this, Column A is a long list of events happening on the specified date/time. I would like to count how many events happen (F) in a timeframe of 10 minutes (between C and D). Some helper columns are made to come to the result in column F (Column B en E)
In Excel the following calculations are applied, but perhaps the calcaluation could be made way easier in PowerBI. Hope somebody could help me out with this one! If more info is needed please let me know.
A = raw data
B = VLOOKUP(A2;C$2:E$8527;3)
C = A
D = A + 10 minutes
E = Index column
F= COUNTIF(B:B;E2)
| Column A | ColumnB | ColumnC | ColumnD | ColumnE | ColumnF |
| Timestamp | Count | Timestart | Time + 10 minutes | Index | Slice count |
| 1-09-20 0:00:04 | 1 | 1:9:2020 00:00:04 | 1:9:2020 00:10:04 | 1 | 1 |
| 1-09-20 0:02:48 | 2 | 1:9:2020 00:02:48 | 1:9:2020 00:12:48 | 2 | 1 |
| 1-09-20 0:14:07 | 3 | 1:9:2020 00:14:07 | 1:9:2020 00:24:07 | 3 | 1 |
| 1-09-20 0:24:49 | 4 | 1:9:2020 00:24:07 | 1:9:2020 00:34:07 | 4 | 3 |
| 1-09-20 0:27:04 | 4 | 1:9:2020 00:34:07 | 1:9:2020 00:44:07 | 5 | 1 |
| 1-09-20 0:31:37 | 4 | 1:9:2020 00:44:07 | 1:9:2020 00:54:07 | 6 | 0 |
| 1-09-20 0:35:04 | 5 | 1:9:2020 00:54:07 | 1:9:2020 01:04:07 | 7 | 0 |
| 1-09-20 1:15:35 | 9 | 1:9:2020 01:04:07 | 1:9:2020 01:14:07 | 8 | 0 |
| 1-09-20 1:19:23 | 9 | 1:9:2020 01:14:07 | 1:9:2020 01:24:07 | 9 | 2 |
| 1-09-20 1:24:08 | 10 | 1:9:2020 01:24:07 | 1:9:2020 01:34:07 | 10 | 3 |
| 1-09-20 1:25:45 | 10 | 1:9:2020 01:34:07 | 1:9:2020 01:44:07 | 11 | 1 |
| 1-09-20 1:33:30 | 10 | 1:9:2020 01:44:07 | 1:9:2020 01:54:07 | 12 | 1 |
3 Replies
- mahoneypat
Microsoft Employee
This can be done in query, but these type of calculations are much better done with DAX. Why do you need this done in the query? Do you plan to do further data transformations using it? Please explain what your final goal is and maybe a different approach can be suggested.
Pat
The reason why I would like to transform it because in DAX I keep getting a calculation error (dataset is over 2MM rows and growing). It was suggested to put the calculation in powerquery to make it easily scalable towards a dataflow (which should be used for large calculations rather than performing them in PowerBI itself). Please let me know your thoughts on this
Hello, not sure if this needs to be another wuestion or not, but I pulling data as an admin on datasets. (when and what time they are refreshed). I am trying to count the number of datasets being refreshed every 30 min from 6AM to 10AM daily. I have a column for dataset name, date it was refreshed and start and end time for each one. Again, trying to determin number of items being refreshed.
