Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 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. 

 

NCon_0-1672040959103.png 

NCon_1-1672040987604.png

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

 

Some direction would be greatly appreciated.

4 REPLIES 4
AlB
Super User
Super User

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?

SU18_powerbi_badge

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
Not applicable

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

 

Hi @Anonymous 

 

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

vjingzhang_0-1672129093275.png

 

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
Not applicable

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors