Clipping/splitting data between it's start and end date/time

[Anonymous]

Hi,

I have a question around working with data with a start and end date and time. These track the duration of events and when they occur.

I'm trying to determine the best way to arrange the data for generating reports across multiple time frames (1 Year, 3 Months, down to a minimum of 1 week). The week is based on a 24/7 runtime with the new week starting every Monday 6:00am

My inital thought was to find events in power query that crossed this time frame and somehow split the events into 2 rows (or 3 or 4 if they lasted over 1 week). I've used alot of if statements, splitting up columns of dates etc and still haven't got to the row splitting yet.

if 
([Start Time Week of Year]=[End Time Week of Year] and [Week of new start date]<[Week of new end date]) then "Split 1"
else if
([Start Day of Week]>0 and [Week of new start date]<[Week of new end date]) then "Split 2"
else if 
([Week of new end date]-[Week of new start date]>1)

then "Split 3" else "OK"

Is there a faster way to do this? Could an event and it's value be clipped based on the desired date range without the need to modify the data? My intentions were to have a slicer for visuals that only ever starts from a Monday 6:00am to another Monday 6:00am. 

 

If clipping or splitting the data, duration, the week and possibly the year would need to be modified.

Some direction would be greatly appreciated.

[AlB]

Hi @Anonymous 

Can you share the input data in, rather than on a screen cap, text-tabular format so that the contents can be copiedand some tests run?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Hello AIB,

Thanks for your response. Is the following table useful? (I've removed irrelevant data) Columns J to W were additional columns formed in power query. Columns R & T are equal to B & C minus 6 Hours respectively.

Id	StartTime	EndTime	Duration	Classification	Week	Month	Year	Start Date	Start Time	End Date	End Time	Start Day of Week	End Day of Week	Start Time Week of Year	End Time Week of Year	Start Date minus 6H	Week of start date minus 6H	End date Minus 6H	Week of end date minus 6H	Identify Rows to Split	Duration (Mins)
25919	7/03/2020 9:00	9/03/2020 6:00	161951	External	10	Mar	2020	Saturday, 7 March 2020	9:00:49 AM	Monday, 9 March 2020	6:00:00 AM	5	0	10	11	7/03/2020 3:00	10	9/03/2020 0:00	11	Split 2	2699
36487	29/01/2022 8:48	12/02/2022 7:07	1203526	External	53	Dec	2022	Saturday, 29 January 2022	8:48:50 AM	Saturday, 12 February 2022	7:07:36 AM	5	5	5	7	29/01/2022 2:48	5	12/02/2022 1:07	7	Split 2	20059
36898	5/03/2022 19:29	14/03/2022 7:03	732818	External	53	Dec	2022	Saturday, 5 March 2022	7:29:28 PM	Monday, 14 March 2022	7:03:06 AM	5	0	10	12	5/03/2022 13:29	10	14/03/2022 1:03	12	Split 2	12214
30195	28/09/2020 1:32	30/09/2020 4:00	181674	External	39	Sep	2020	Monday, 28 September 2020	1:32:06 AM	Wednesday, 30 September 2020	4:00:00 AM	0	2	40	40	27/09/2020 19:32	39	29/09/2020 22:00	40	Split 1	3028
30334	12/10/2020 0:00	14/10/2020 6:00	194380	External	41	Oct	2020	Monday, 12 October 2020	12:00:20 AM	Wednesday, 14 October 2020	6:00:00 AM	0	2	42	42	11/10/2020 18:00	41	14/10/2020 0:00	42	Split 1	3240
25710	18/02/2020 7:38	18/02/2020 14:55	26224	External	8	Feb	2020	Tuesday, 18 February 2020	7:38:41 AM	Tuesday, 18 February 2020	2:55:45 PM	1	1	8	8	18/02/2020 1:38	8	18/02/2020 8:55	8	OK	437
25712	18/02/2020 7:49	22/02/2020 8:26	347809	External	8	Feb	2020	Tuesday, 18 February 2020	7:49:41 AM	Saturday, 22 February 2020	8:26:30 AM	1	5	8	8	18/02/2020 1:49	8	22/02/2020 2:26	8	OK	5797
25713	18/02/2020 14:55	18/02/2020 14:57	120	External	8	Feb	2020	Tuesday, 18 February 2020	2:55:45 PM	Tuesday, 18 February 2020	2:57:45 PM	1	1	8	8	18/02/2020 8:55	8	18/02/2020 8:57	8	OK	2
25714	18/02/2020 14:57	21/02/2020 13:22	253473	External	8	Feb	2020	Tuesday, 18 February 2020	2:57:45 PM	Friday, 21 February 2020	1:22:18 PM	1	4	8	8	18/02/2020 8:57	8	21/02/2020 7:22	8	OK	4225
25726	21/02/2020 13:28	22/02/2020 8:26	68292	External	8	Feb	2020	Friday, 21 February 2020	1:28:18 PM	Saturday, 22 February 2020	8:26:30 AM	4	5	8	8	21/02/2020 7:28	8	22/02/2020 2:26	8	OK	1138

[v-jingzhang]

Hi @Anonymous 

I add four columns with some existing columns in your data. Please download the attachment to see detailed transformation steps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[Anonymous]

Hi v-jingzhang, that's a great solution thanks! I did just added on a couple rows to test some rare but possible situations. The first held up well ID 66666 (Duration that runs over a 2 week span) but I get an error around a situation where an events crosses into a new year ID 77777. Would you have any thoughts on how to manage that? 

My initial thought is the list should at least generate a 50, 51, 52, 1?

66666	18/12/2022 13:28	29/12/2022 8:26	932292	External	50	Dec	2022	Sunday, 18 December 2022	1:28:18 PM	Thursday, 29 December 2022	8:26:30 AM	6	3	50	52	18/12/2022 7:28	50	29/12/2022 2:26	52	Split 3	15538.2
77777	18/12/2022 13:28	5/1/2023 8:26	1537092	External	50	Dec	2022	Sunday, 18 December 2022	1:28:18 PM	Thursday, 5 January 2023	8:26:30 AM	6	3	50	1	18/12/2022 7:28	50	05/01/2023 2:26	1	Split 3	25618.2