Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

Split start time and end time into multiple rows based on shift



I'm trying to achieve the transformation below. I tried multiple things but they quickly became overly complicated. I hope you can help.


My sample data:


IdStart TimeEnd Time
12020-01-05 15:20:002020-01-05 22:45:00
22020-01-11 10:20:002020-01-12 08:00:00
32020-01-14 16:55:002020-01-15 08:00:00
32020-01-15 08:00:002020-01-16 08:00:00


There are three shifts per day starting from 08:00 AM and ending at 08:00 AM on next day, 8 hours each.


Shift 1 - 08-16

Shift 2 - 16-00

Shift 3 - 00-08


I'd like to split each row if the event spans over multiple shifts and replace start and end times with shift start and end times until start time and end time is on the same shift.


Desired outcome:


IdStart TimeEnd Time
12020-01-05 15:20:002020-01-05 16:00:00
12020-01-05 16:00:002020-01-05 22:45:00
22020-01-11 10:20:002020-01-11 16:00:00
22020-01-11 16:00:002020-01-12 00:00:00
22020-01-12 00:00:002020-01-12 08:00:00
32020-01-14 16:55:002020-01-15 00:00:00
32020-01-15 00:00:002020-01-15 08:00:00
32020-01-15 08:00:002020-01-15 16:00:00
32020-01-15 16:00:002020-01-16 00:00:00
32020-01-16 00:00:002020-01-16 08:00:00


Thank you in advance!

Not applicable

try this and let's know

View solution in original post


I am looking for the same solution, did you figure out how?

Not applicable

try this and let's know

It works 0K
I tried to modify it to work with shifts like this:
Shift 1 => 07:00 - 15:00
Shift 2 => 15:00 - 23:00
Shift 3=> 23:00 - 07:00 (next day)
but couldn't find the solution,
Can you, or someone, help, please?

Thank your for solution. I haven't yet tried with my data but it should work. Can you please walk me through your steps? I'd like to understand better for future uses. Would this work with 12h shifts for example?

Not applicable

try to adapt and see what is appening .

replace "08" with "12".

consider that where there is a product of 3 it is, in effect, of 24/8. In the hypothesis of two shifts of 12 hours, 24/8 becomes 24/12 = 2.


Regarding the explanation, it depends on your level of knowledge.

I can explain the basic idea of the solution or even the implementation details (there are also possibilities of different solutions using the List.generate function).


If you try to study it and then say where it is not clear to you, perhaps the explanation could be more effective.

Thanks for your explanation. I'm not too familiar with M but so far I can follow the transformations. Accepted as solution.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors