How to split one row into multiple rows based on time of day condition

[geiratatea]

Hi!

I have a use case where I want to calculate an hourly rate based on whether the work takes place during normal working hours, and the work crosses normal hours and goes into overtime and only overtime hours. Normal time is between 8-16, 50% overtime is between 16-19 and 100% overtime is between 19-08 the next day (an extension applies to 100% overtime on public holidays).

But let's take the first. A row consists of a time stamp (start time) and a period length in seconds (divided by 3600 you get hours). The end time can also be calculated from this. So this is what I want to happen:

• If the work falls within normal time, the row is as before, only tagged with normal time. The same is the case if the work falls
• within 50 overtime and 100% overtime.
• But if the working time crosses normal time and overtime, new rows must be created.

Example 1: if you work from 3:00 PM to 6:00 PM, the original row should show normal time 3:00 PM to 4:00 PM and the new row should show 4:00 PM to 6:00 PM

Example 2: if you work from 3:00 PM to 10:00 PM, the original row should show normal time 3:00 PM to 4:00 PM and the new row should show overtime 50% from 4:00 PM to 7:00 PM and a third row show overtime 100% from 7:00 PM to 10:00 PM

I am attaching test data for this case. Since I use Power BI, this can be solved either with DAX or Power Query. I've seen advanced power query examples that almost do what I want, but I can't quite figure out how to rewrite this in power query

https://community.fabric.microsoft.com/t5/Power-Query/Split-start-time-and-end-time-into-multiple-ro...

The hourly price is then calculated based on a normal price which is then multiplied by 1.5 (50% overtime) or 2 (100% overtime) based on each row.

I hope someone can help me with this case

Best regards, Geir

TimeLog table from source  

Work Item	User Name	Timestamp	PeriodLength	Hours
Change display of gallery in ongoing campaigns	Maria	1/12/2024 9:00:00 AM +00:00	5400	1.5
Routine for checking EPD dates and Customer number associated with campaign	Maria	1/15/2024 5:30:00 PM +00:00	7200	2
Change the JSON schema in flow app.HistoricCampaign to contain subsidies	Erlend	1/18/2024 3:30:00 PM +00:00	3600	1
Change items in galleries in current and previous campaigns to take filter subsidies into account	Maria	1/19/2024 3:30:00 PM +00:00	21600	6

TimeLog table after transformations

Work Item	User Name	Timestamp (org)	PeriodLength	Hours	Start Time	End Time	Time Type
Change display of gallery in ongoing campaigns	Maria	1/12/2024 9:00:00 AM +00:00	5400	1.5	1/12/2024 9:00:00 AM +00:00	1/12/2024 10:30:00 AM +00:00	Regular
Routine for checking EPD dates and Customer number associated with campaign	Maria	1/15/2024 5:30:00 PM +00:00	5400	1.5	1/15/2024 5:30:00 PM +00:00	1/15/2024 7:00:00 PM +00:00	Overtime 50%
Routine for checking EPD dates and Customer number associated with campaign	Maria	1/15/2024 5:30:00 PM +00:00	1800	0.5	1/15/2024 7:00:00 PM +00:00	1/15/2024 7:30:00 PM +00:00	Overtime 100%
Change the JSON schema in flow app.HistoricCampaign to contain subsidies	Erlend	1/18/2024 3:30:00 PM +00:00	1800	0.5	1/18/2024 3:30:00 PM +00:00	1/18/2024 4:00:00 PM +00:00	Regular
Change the JSON schema in flow app.HistoricCampaign to contain subsidies	Erlend	1/18/2024 3:30:00 PM +00:00	1800	0.5	1/18/2024 4:00:00 PM +00:00	1/18/2024 4:30:00 PM +00:00	Overtime 50%
Change items in galleries in current and previous campaigns to take filter subsidies into account	Maria	1/19/2024 3:30:00 PM +00:00	1800	0.5	1/19/2024 3:30:00 PM +00:00	1/19/2024 4:00:00 PM +00:00	Regular
Change items in galleries in current and previous campaigns to take filter subsidies into account	Maria	1/19/2024 3:30:00 PM +00:00	10800	3	1/19/2024 4:00:00 PM +00:00	1/19/2024 7:00:00 PM +00:00	Overtime 50%
Change items in galleries in current and previous campaigns to take filter subsidies into account	Maria	1/19/2024 3:30:00 PM +00:00	9000	2.5	1/19/2024 7:00:00 PM +00:00	1/19/2024 9:30:00 PM +00:00	Overtime 100%

[v-junyant-msft]

Hi @geiratatea ,

May I ask what your judgment criteria are?
I don't really understand the results of this line of data:

Why does it span 1 day but it results in Regular for the full time period?

Best Regards,
Dino Tao

[geiratatea]

Hello and thanks for your reply. The use case here is about calculating ordinary hours and overtime hours which have different price rates. It is rare for a consultant to work more than 24 hours continuously. So the most common thing is that you work 8 hours. But the overtime limits are often set at specific times. Let's say that the hours 176 to 19 give 50% overtime, and the period after 19 gives 100%. And then there are public holidays where there is 100% overtime around the clock.

It is possible that I am complicating the task too much. But simply put, I either have to create a row with different columns for the different intervals to find how many hours from start to finish fall in the different intervals. Or do I have to generate new rows after which the time slips over several intervals.