Forum Discussion
Split Rows Based On Start/End Time. New Row per Day
f = (row as record) as list => if Date.From(row[End Date Time]) = Date.From(row[Start Date Time]) then row else [max_dt = row[End Date Time], gen = List.Generate( () => Record.TransformFields(row, {"End Date Time", (w) => Date.StartOfDay(Date.AddDays(row[Start Date Time], 1))}), (x) => x[Start Date Time] < max_dt, (x) => [Item Name = x[Item Name], Start Date Time = Date.StartOfDay(Date.AddDays(x[Start Date Time], 1)), End Date Time = List.Min({max_dt, Date.AddDays(x[End Date Time], 1)})] )][gen], tbl = Table.TransformRows(your_table, f), z = Table.FromRecords(List.Combine(tbl))
f = (row as record) as list =>
if Date.From(row[End Date Time]) = Date.From(row[Start Date Time]) then row else
[max_dt = row[End Date Time],
gen = List.Generate(
() => Record.TransformFields(row, {"End Date Time", (w) => Date.StartOfDay(Date.AddDays(row[Start Date Time], 1))}),
(x) => x[Start Date Time] < max_dt,
(x) =>
[Item Name = x[Item Name],
Start Date Time = Date.StartOfDay(Date.AddDays(x[Start Date Time], 1)),
End Date Time = List.Min({max_dt, Date.AddDays(x[End Date Time], 1)})]
)][gen],
tbl = Table.TransformRows(your_table, f),
z = Table.FromRecords(List.Combine(tbl))
Hi!
I have a similar 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).
Perhaps I can reuse the first step creating new days first. It is maybe very rare that you are working over 24 hours in once, but let us say we want to make it general.
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
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% |