Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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 dat...
Need Help
Tutorial Requests
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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.

 

IdStartTimeEndTimeDurationClassificationWeekMonthYearStart DateStart TimeEnd DateEnd TimeStart Day of WeekEnd Day of WeekStart Time Week of YearEnd Time Week of YearStart Date minus 6HWeek of start date minus 6HEnd date Minus 6HWeek of end date minus 6HIdentify Rows to SplitDuration (Mins)
259197/03/2020 9:009/03/2020 6:00161951External10Mar2020Saturday, 7 March 20209:00:49 AMMonday, 9 March 20206:00:00 AM5010117/03/2020 3:00109/03/2020 0:0011Split 22699
3648729/01/2022 8:4812/02/2022 7:071203526External53Dec2022Saturday, 29 January 20228:48:50 AMSaturday, 12 February 20227:07:36 AM555729/01/2022 2:48512/02/2022 1:077Split 220059
368985/03/2022 19:2914/03/2022 7:03732818External53Dec2022Saturday, 5 March 20227:29:28 PMMonday, 14 March 20227:03:06 AM5010125/03/2022 13:291014/03/2022 1:0312Split 212214
3019528/09/2020 1:3230/09/2020 4:00181674External39Sep2020Monday, 28 September 20201:32:06 AMWednesday, 30 September 20204:00:00 AM02404027/09/2020 19:323929/09/2020 22:0040Split 13028
3033412/10/2020 0:0014/10/2020 6:00194380External41Oct2020Monday, 12 October 202012:00:20 AMWednesday, 14 October 20206:00:00 AM02424211/10/2020 18:004114/10/2020 0:0042Split 13240
2571018/02/2020 7:3818/02/2020 14:5526224External8Feb2020Tuesday, 18 February 20207:38:41 AMTuesday, 18 February 20202:55:45 PM118818/02/2020 1:38818/02/2020 8:558OK437
2571218/02/2020 7:4922/02/2020 8:26347809External8Feb2020Tuesday, 18 February 20207:49:41 AMSaturday, 22 February 20208:26:30 AM158818/02/2020 1:49822/02/2020 2:268OK5797
2571318/02/2020 14:5518/02/2020 14:57120External8Feb2020Tuesday, 18 February 20202:55:45 PMTuesday, 18 February 20202:57:45 PM118818/02/2020 8:55818/02/2020 8:578OK2
2571418/02/2020 14:5721/02/2020 13:22253473External8Feb2020Tuesday, 18 February 20202:57:45 PMFriday, 21 February 20201:22:18 PM148818/02/2020 8:57821/02/2020 7:228OK4225
2572621/02/2020 13:2822/02/2020 8:2668292External8Feb2020Friday, 21 February 20201:28:18 PMSaturday, 22 February 20208:26:30 AM458821/02/2020 7:28822/02/2020 2:268OK1138

 

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?

       

      6666618/12/2022 13:2829/12/2022 8:26932292External50Dec2022Sunday, 18 December 20221:28:18 PMThursday, 29 December 20228:26:30 AM63505218/12/2022 7:285029/12/2022 2:2652Split 315538.2
      7777718/12/2022 13:285/1/2023 8:261537092External50Dec2022Sunday, 18 December 20221:28:18 PMThursday, 5 January 20238:26:30 AM6350118/12/2022 7:285005/01/2023 2:261Split 325618.2