Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello community members,
I am trying to achieve a somehow complicated task on Power BI.
I have a table monitoring activities. A sample can be found below:
StartDate | StartTime | EndDate | EndTime | Id |
Donnerstag, 13. April 2023 | 10:30:06 | Donnerstag, 13. April 2023 | 10:54:46 | 1161 |
Donnerstag, 13. April 2023 | 12:30:00 | Donnerstag, 13. April 2023 | 16:36:00 | 1162 |
Freitag, 5. Mai 2023 | 23:00:03 | Samstag, 6. Mai 2023 | 00:50:20 | 1186 |
Dienstag, 9. Mai 2023 | 23:00:06 | Mittwoch, 10. Mai 2023 | 00:50:28 | 1187 |
Montag, 8. Mai 2023 | 23:00:00 | Dienstag, 9. Mai 2023 | 00:51:03 | 1188 |
I have also created the 'HourlyIntervals' table
Value | HourlyIntervalStart | HourlyIntervalEnd | IntervalDuration |
0 | 00:00:00 | 01:00:00 | 01:00:00 |
1 | 01:00:00 | 02:00:00 | 01:00:00 |
2 | 02:00:00 | 03:00:00 | 01:00:00 |
3 | 03:00:00 | 04:00:00 | 01:00:00 |
4 | 04:00:00 | 05:00:00 | 01:00:00 |
5 | 05:00:00 | 06:00:00 | 01:00:00 |
6 | 06:00:00 | 07:00:00 | 01:00:00 |
7 | 07:00:00 | 08:00:00 | 01:00:00 |
8 | 08:00:00 | 09:00:00 | 01:00:00 |
9 | 09:00:00 | 10:00:00 | 01:00:00 |
10 | 10:00:00 | 11:00:00 | 01:00:00 |
11 | 11:00:00 | 12:00:00 | 01:00:00 |
12 | 12:00:00 | 13:00:00 | 01:00:00 |
13 | 13:00:00 | 14:00:00 | 01:00:00 |
14 | 14:00:00 | 15:00:00 | 01:00:00 |
15 | 15:00:00 | 16:00:00 | 01:00:00 |
16 | 16:00:00 | 17:00:00 | 01:00:00 |
17 | 17:00:00 | 18:00:00 | 01:00:00 |
18 | 18:00:00 | 19:00:00 | 01:00:00 |
19 | 19:00:00 | 20:00:00 | 01:00:00 |
20 | 20:00:00 | 21:00:00 | 01:00:00 |
21 | 21:00:00 | 22:00:00 | 01:00:00 |
22 | 22:00:00 | 23:00:00 | 01:00:00 |
23 | 23:00:00 | 00:00:00 | 01:00:00 |
What I want to achieve is:
I want to generate, for each activity of my first table, dynamic rows according to the activity duration. For each row, the duration within the hour should be calculated. I will give 3 possible situations with the expected results:
1. For Id = 1161 below:
StartDate | StartTime | EndDate | EndTime | Id |
Donnerstag, 13. April 2023 | 10:30:06 | Donnerstag, 13. April 2023 | 10:54:46 | 1161 |
Expected results: I have StartDate = EndDate, the activity belong to the hourly interval from 10:00:00 to 11:00:00, so one row should be generated with HourlyIntervalStart = 10:00:00, HourlyIntervalEnd = 11:00:00, Duration = EndTime - StartTime = 00:24:40.
2. For Id = 1162 below:
StartDate | StartTime | EndDate | EndTime | Id |
Donnerstag, 13. April 2023 | 12:30:05 | Donnerstag, 13. April 2023 | 16:36:53 | 1162 |
I have StartDate = EndDate and the activity belongs to 5 intervals, so I expect the results below (special attention to the duration):
StartDate | StartTime | EndDate | EndTime | Id | HourlyIntervalStart | HourlyIntervalEnd | Duration |
Donnerstag, 13. April 2023 | 12:30:00 | Donnerstag, 13. April 2023 | 16:36:00 | 1162 | 12:00:00 | 13:00:00 | 00:30:00 |
Donnerstag, 13. April 2023 | 12:30:00 | Donnerstag, 13. April 2023 | 16:36:00 | 1162 | 13:00:00 | 14:00:00 | 01:00:00 |
Donnerstag, 13. April 2023 | 12:30:00 | Donnerstag, 13. April 2023 | 16:36:00 | 1162 | 14:00:00 | 15:00:00 | 01:00:00 |
Donnerstag, 13. April 2023 | 12:30:00 | Donnerstag, 13. April 2023 | 16:36:00 | 1162 | 15:00:00 | 16:00:00 | 01:00:00 |
Donnerstag, 13. April 2023 | 12:30:00 | Donnerstag, 13. April 2023 | 16:36:00 | 1162 | 16:00:00 | 17:00:00 | 00:24:00 |
3. The third scenario is when StartDate <> EndDate, like the Id below:
StartDate | StartTime | EndDate | EndTime | Id |
Montag, 8. Mai 2023 | 23:00:00 | Dienstag, 9. Mai 2023 | 00:51:03 | 1188 |
Expected result: This is similar to scenario 2, but as the day is different, the calculation needs to consider it in order to result in the following 2 rows:
StartDate | StartTime | EndDate | EndTime | Id | HourlyIntervalStart | HourlyIntervalEnd | Duration |
Montag, 8. Mai 2023 | 23:00:00 | Dienstag, 9. Mai 2023 | 00:51:03 | 1188 | 23:00:00 | 00:00:00 | 01:00:00 |
Montag, 8. Mai 2023 | 23:00:00 | Dienstag, 9. Mai 2023 | 00:51:03 | 1188 | 00:00:00 | 01:00:00 | 00:08:57 |
I would appreciate your suggestions. Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
Here are the steps you can follow:
1. Use Custom Columns in Power Query to stitch together Date and Time.
Date.ToText([StartDate])&" "&Time.ToText([StartTime])
2. [Customer] -- Transform – Date/Time
3. The same operation yields the End Date.
4. Add Column – Custom Column -- Create three columns to get the hour, minute, and index
(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL]))
(Duration.Minutes([EndTimeALL]- [StartTimeALL]))
(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL]))
+1
5. Create table
6. Merge table.
Home – Merge Queries -- Merge Queries as New
7. Click on the extension icon and only this [lndex] -- OK
Result:
8. Create calculated column:
Duration =
var _max=
MAXX(
FILTER(ALL(Merge1),
'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index])
var _min=
MINX(
FILTER(ALL(Merge1),
'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index])
var _value=
MAXX( FILTER(ALL(Merge1),'Merge1'[Custom]=EARLIER('Merge1'[Custom])&&'Merge1'[True_Table.Index]=_min),[StartTime])
var _flag=
DATEDIFF(
_value,
TIME(
HOUR(_value)+1,0,0),MINUTE)
var _count=
COUNTX(
FILTER(ALL(Merge1),
'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index])
return
IF(
_count = 1,
TIME(
0,[Minute],0),
IF(
'Merge1'[True_Table.Index]=_min && _flag>0,
TIME(0,_flag,0),
IF(
'Merge1'[True_Table.Index]=_max,
TIME(
0,[Minute],0),
TIME(1,0,0))))
9. Create calculated table:
Table =
SUMMARIZE(
'Merge1', 'Merge1'[StartDate],'Merge1'[StartTime],'Merge1'[EndDate],'Merge1'[EndTime],'Merge1'[Id],'Merge1'[Duration])
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@v-eqin-msft thanks a lot, with slight modifications for my data it worked perfectly!
Hi @Anonymous ,
Here are the steps you can follow:
1. Use Custom Columns in Power Query to stitch together Date and Time.
Date.ToText([StartDate])&" "&Time.ToText([StartTime])
2. [Customer] -- Transform – Date/Time
3. The same operation yields the End Date.
4. Add Column – Custom Column -- Create three columns to get the hour, minute, and index
(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL]))
(Duration.Minutes([EndTimeALL]- [StartTimeALL]))
(Duration.Days([EndTimeALL] - [StartTimeALL]) *24 ) + (Duration.Hours([EndTimeALL] - [StartTimeALL]))
+1
5. Create table
6. Merge table.
Home – Merge Queries -- Merge Queries as New
7. Click on the extension icon and only this [lndex] -- OK
Result:
8. Create calculated column:
Duration =
var _max=
MAXX(
FILTER(ALL(Merge1),
'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index])
var _min=
MINX(
FILTER(ALL(Merge1),
'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index])
var _value=
MAXX( FILTER(ALL(Merge1),'Merge1'[Custom]=EARLIER('Merge1'[Custom])&&'Merge1'[True_Table.Index]=_min),[StartTime])
var _flag=
DATEDIFF(
_value,
TIME(
HOUR(_value)+1,0,0),MINUTE)
var _count=
COUNTX(
FILTER(ALL(Merge1),
'Merge1'[Custom]=EARLIER('Merge1'[Custom])),[True_Table.Index])
return
IF(
_count = 1,
TIME(
0,[Minute],0),
IF(
'Merge1'[True_Table.Index]=_min && _flag>0,
TIME(0,_flag,0),
IF(
'Merge1'[True_Table.Index]=_max,
TIME(
0,[Minute],0),
TIME(1,0,0))))
9. Create calculated table:
Table =
SUMMARIZE(
'Merge1', 'Merge1'[StartDate],'Merge1'[StartTime],'Merge1'[EndDate],'Merge1'[EndTime],'Merge1'[Id],'Merge1'[Duration])
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |