Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have data that needs to be split on a daily basis.
Item Name | Start Date Time | End Date Time | ||
A | 11/19/2023 11:15:00 PM | 11/20/2023 1:15:00 AM | ||
B | 11/20/2023 02:15:00 PM | 11/22/2023 5:00 AM | ||
I would like to take data similar to the table above and produce the output below.
Item Name | Start Date Time | End Date Time | ||
A | 11/19/2023 11:15:00 PM | 11/20/2023 12:00:00 AM | ||
A | 11/20/2023 12:00:00 AM | 11/20/2023 1:15:00 AM | ||
B | 11/20/2023 02:15:00 PM | 11/21/2023 12:00:00 AM | ||
B | 11/21/2023 12:00:00 AM | 11/22/2023 12:00:00 AM | ||
B | 11/22/2023 12:00:00 AM | 11/22/2023 5:00:00 AM |
Solved! Go to Solution.
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 @gabereal, another approach.
Restult:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01De01DcyMDJWMDS0MjS1MjBQCPCFSBgZQCWg4o6+SrE60UpOqLIGRujajCASyHqcoVImuPSYoOmJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Name" = _t, #"Start Date Time" = _t, #"End Date Time" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Start Date Time", type datetime}, {"End Date Time", type datetime}}, "en-US"),
Ad_NewStartEnd = Table.AddColumn(ChangedType, "New StartEnd", each
[ start = Date.From([Start Date Time]),
end = Date.From([End Date Time]),
dates = List.Dates(Date.AddDays(start, 1), Duration.TotalDays(end - start) , #duration(1,0,0,0)),
startCalc = if List.Count(dates) > 0 then {[Start Date Time]} & List.Transform(dates, each DateTime.From(_ & #time(0,0,0))) else {[Start Date Time]},
endCalc = if List.Count(dates) > 0 then {DateTime.From(List.First(dates) & #time(0,0,0))} & List.Transform(List.RemoveLastN(dates, 1), each DateTime.From(Date.AddDays(_, 1) & #time(0,0,0))) & {[End Date Time]} else {[End Date Time]},
toTable = Table.FromColumns({startCalc, endCalc}, type table[Start New = date, End New = date])
][toTable], type table),
RemovedColumns = Table.RemoveColumns(Ad_NewStartEnd,{"Start Date Time", "End Date Time"}),
ExpandedNewStartEnd = Table.ExpandTableColumn(RemovedColumns, "New StartEnd", {"Start New", "End New"}, {"Start Date Time", "End Date Time"}),
ChangedType2 = Table.TransformColumnTypes(ExpandedNewStartEnd,{{"Start Date Time", type datetime}, {"End Date Time", type datetime}})
in
ChangedType2
Here is a way to do it without going into the advanced editor and taking advantage of how power query will generate a list from 1 to 10 if you type {1 .. 10} :
Duplicate the start and end date columns.
Change columns types to decimal
Add custom column with the formula:
Table.FromColumns(
{
{
[#"Start Date Time - Copy"],
Number.RoundUp([#"Start Date Time - Copy"],0)
..
Number.RoundDown([#"End Date Time - Copy"],0)
},
{
Number.RoundUp([#"Start Date Time - Copy"],0)
..
Number.RoundDown([#"End Date Time - Copy"],0),
[#"End Date Time - Copy"]
}
},
{"start","end"}
)
Expand.
Change to datetime.
Add custom column to detect for same start/end times and filter out.
Here's another method using List.Accumulate
Data
let
//Change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Item Name", type text}, {"Start Date Time", type datetime}, {"End Date Time", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
let
//create list of included days
dys = List.DateTimes(
Date.StartOfDay([Start Date Time]),
Duration.Days(Date.EndOfDay([End Date Time]) - Date.StartOfDay([Start Date Time]))+1,
#duration(1,0,0,0)),
//create List of datetimes associated with each day
// then add the last [End Date Time] to the list
dts = List.Accumulate(
dys,
{},
(s,c)=> s & {List.Max({[Start Date Time],c})}) & {[End Date Time]},
//Turn the list into a Table by shifting the list up one to
// reflect start and end date/times
tbl = Table.FromColumns(
{List.RemoveLastN(dts,1)} &
{List.RemoveFirstN(dts,1)},
{"Start Date Time", "End Date Time"})
in tbl, type table[Start Date Time=datetime, End Date Time=datetime]),
//Remove original datetime columns
// then expand the generated table list
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date Time", "End Date Time"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Start Date Time", "End Date Time"})
in
#"Expanded Custom"
Results
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:
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% |
Hi @geiratatea, try this.
Holidays are not included fow now. You should have such holidays table - if you have it, provide it here and I can implement holidays too.
You can edit times at Times step (but please preserve the logic)
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBbS8NAEIX/ypBXi82lUds3iQURqkUfQx+mm00yNNkNe7H03zu5UA1UWJgdZne+c06eB1mNqpJQkO0avIAuocKmkeYCpECrSpOqQGDbIVXKBotgh4aQa7SM4mUcxitYb8KQDzzv4G648TRdcTks8uBTe0dKQqkNiFqKU79vu3+BAp20gKqAzFunW2lA+fbIBa3VgnhcwJlcfaXP4ekITzfJAN//gT/GE3wy52oJb18f72BZQYu9s7LRZ8Cuu38lhhsS2QQBp0Fo5ZAfWX+0VJDsbW9NI1Uxop9GdHIDnTzM0eRka3vgmCrv6hvhjZHKDe47I79Je/sbci/B4Ykzo8ZxHlcZ/JVHKIT2ys3TWP8vKY4GTYcf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Item" = _t, #"User Name" = _t, Timestamp = _t, PeriodLength = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Timestamp", type datetimezone}, {"PeriodLength", Int64.Type}}, "en-US"),
Times = [ R_Start = #time(8, 0, 0),
R_End = #time(16, 0, 0),
O1_Start = R_End,
O1_End = #time(19, 0, 0),
O2_Start = O1_End,
O2_End = #time(8, 0, 0) ],
MinutesList = [ R_Minutes = List.Buffer(List.Times(Times[R_Start],
if Times[R_End] < Times[R_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[R_Start] + Times[R_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[R_End] - Times[R_Start]) +1, #duration(0,0,1,0))),
O1_Minutes = List.Buffer(List.Times(Times[O1_Start],
if Times[O1_End] < Times[O1_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[O1_Start] + Times[O1_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[O1_End] - Times[O1_Start]) +1, #duration(0,0,1,0))),
O2_Minutes = List.Buffer(List.Times(Times[O2_Start],
if Times[O2_End] < Times[O2_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[O2_Start] + Times[O2_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[O2_End] - Times[O2_Start]) +1, #duration(0,0,1,0)))
],
StepBack = ChangedType,
Ad_StartTime = Table.DuplicateColumn(StepBack, "Timestamp", "Start Time"),
Ad_EndTime = Table.AddColumn(Ad_StartTime, "End Time", each [Start Time] + #duration(0,0,0,[PeriodLength]), DateTimeZone.Type),
Ad_WorkingMinutesRecord = Table.AddColumn(Ad_EndTime, "Working Minutes", each
[ TimeStart = DateTime.Time([Start Time]),
TimeEnd = DateTime.Time([End Time]),
WokrkingMinutes = List.Times(TimeStart, if TimeEnd < TimeStart then Duration.TotalMinutes(#time(23, 59, 0) - TimeStart + TimeEnd - #time(0, 0, 0)) +2 else Duration.TotalMinutes(TimeEnd - TimeStart) +1, #duration(0,0,1,0)),
R_WorkingMinutes = List.Count(List.Intersect({MinutesList[R_Minutes], WokrkingMinutes})) -1,
O1_WorkingMinutes = List.Count(List.Intersect({MinutesList[O1_Minutes], WokrkingMinutes})) -1,
O2_WorkingMinutes = List.Count(List.Intersect({MinutesList[O2_Minutes], WokrkingMinutes})) -1
][[R_WorkingMinutes], [O1_WorkingMinutes], [O2_WorkingMinutes]], type record),
TransformWorkingMinutesRecord = Table.TransformColumns(Ad_WorkingMinutesRecord, {{"Working Minutes", each Table.SelectRows(Record.ToTable(_), (x)=> x[Value] > 0), type table}}),
ExpandedWorkingMinutesTable = Table.ExpandTableColumn(TransformWorkingMinutesRecord, "Working Minutes", {"Name", "Value"}, {"Time Type", "Hours"}),
TransformColumns = Table.TransformColumns(ExpandedWorkingMinutesTable,
{ { "Time Type", each if Text.StartsWith(_, "R_") then "Regular" else if Text.StartsWith(_, "O1_") then "Overtime 50%" else "Overtime 100%", type text },
{ "Hours", each _ / 60, type number } } ),
Ad_Coefficient = Table.AddColumn(TransformColumns, "Coefficient", each if [Time Type] = "Regular" then 1 else if [Time Type] = "Overtime 50%" then 1.5 else 2, type number)
in
Ad_Coefficient
Hi again
And thanks for your genious solution on my problem. Here is a list of norwegian 2024 holiday days. I guess this had to be related to an ordinary date table, but here it is. If you could reuse this in the solution it would be great. I will study your code to learn all the process. Thank you again
DateDay NameHoliday NameType
1/1/2024 | Monday | Første nyttårsdag | Helligdag |
3/28/2024 | Thursday | Skjærtorsdag | Helligdag |
3/29/2024 | Friday | Langfredag | Helligdag |
3/31/2024 | Sunday | Første påskedag | Helligdag |
4/1/2024 | Monday | Andre påskedag | Helligdag |
5/9/2024 | Thursday | Kristi himmelfartsdag | Helligdag |
5/19/2024 | Sunday | Første pinsedag | Helligdag |
5/20/2024 | Monday | Andre pinsedag | Helligdag |
12/25/2024 | Wednesday | Første juledag | Helligdag |
12/26/2024 | Thursday | Andre juledag | Helligdag |
Hi @geiratatea, done.
You can edit holidays via gear icon (see red rectangle) or replace whole selected code with your holiday table reference (but don't forget that you holiday table must contains column called [Date])
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHdagIxEIVfZdirlkrVVdvqXbGCFGyl9U68GJNxd3A3WfJT8e07uyu1lgqBSZjkfGdO1utkRT7AzdwWrPF4m3SSJQVyUvvdfjftpUMYT3o9WfC8gLtmJ83Bg5RNZ51MczQZgWZfFXgEu4MMi4LcEdiANZllk4HCskLOjJeXC3SMrXx6XX80POl/2BjYEOysA5WT2td6s+ULaAzkAY2GafTBluTAxHIrBb23iqWt4cAh/6FfwkctfDQZNPDlL/hjejlcyAleP9/fwIuDEuvJdoU9AFbV/ZwF7lhNTxAIFpQ1AeWSj1vPmqkee+YKMrpFP7XowT/ov7lyoNLXwDZV0aoPKjpHJjTTV46+2EZ/Drm2EHAvmXEhX3m2IU+lhUrZaMJlGuPrltJ+42nzDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Item" = _t, #"User Name" = _t, Timestamp = _t, PeriodLength = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Timestamp", type datetimezone}, {"PeriodLength", Int64.Type}}, "en-US"),
Holidays = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xCoMwFAbgq5TMQkzUUscuUmg7WeggDoFEjcZYkjh4IHuG7l6silhoSbo9Hu/j/X+WAQQRxD4OgQeunaRkmIdkeilt2E4Oxkyj0pSU8/bEhODlMudeBgKIDxu8Vf1ytNC0qaenMp0TxRtKFF/JhciyUMx+H3zSpf1Pusc06sbKQkuno6Tqn4lgbKlzVlwbvqt42zJREGWstSKIYndMLrXjI/ZdMV0GYYijDd0ZlUx/f6t74YR7S8H1n1Xlbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Day Name" = _t, #"Holiday Name" = _t, Type = _t]),
HolidaysFormatted = Table.TransformColumnTypes(Holidays,{{"Date", type date}}, "en-US"),
Times = [ R_Start = #time(8, 0, 0),
R_End = #time(16, 0, 0),
O1_Start = R_End,
O1_End = #time(19, 0, 0),
O2_Start = O1_End,
O2_End = #time(8, 0, 0) ],
MinutesList = [ R_Minutes = List.Buffer(List.Times(Times[R_Start],
if Times[R_End] < Times[R_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[R_Start] + Times[R_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[R_End] - Times[R_Start]) +1, #duration(0,0,1,0))),
O1_Minutes = List.Buffer(List.Times(Times[O1_Start],
if Times[O1_End] < Times[O1_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[O1_Start] + Times[O1_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[O1_End] - Times[O1_Start]) +1, #duration(0,0,1,0))),
O2_Minutes = List.Buffer(List.Times(Times[O2_Start],
if Times[O2_End] < Times[O2_Start] then Duration.TotalMinutes(#time(23, 59, 0) - Times[O2_Start] + Times[O2_End] - #time(0, 0, 0)) +2 else Duration.TotalMinutes(Times[O2_End] - Times[O2_Start]) +1, #duration(0,0,1,0)))
],
StepBack = ChangedType,
Ad_DateHelper = Table.DuplicateColumn(StepBack, "Timestamp", "Date Helper"),
ChangedType2 = Table.TransformColumnTypes(Ad_DateHelper,{{"Date Helper", type date}}),
FilteredOutHolidays = Table.SelectRows(ChangedType2, each not List.Contains(HolidaysFormatted[Date], [Date Helper])),
RemovedDateHelper = Table.RemoveColumns(FilteredOutHolidays,{"Date Helper"}),
Ad_StartTime = Table.DuplicateColumn(RemovedDateHelper, "Timestamp", "Start Time"),
Ad_EndTime = Table.AddColumn(Ad_StartTime, "End Time", each [Start Time] + #duration(0,0,0,[PeriodLength]), DateTimeZone.Type),
Ad_WorkingMinutes = Table.AddColumn(Ad_EndTime, "Working Minutes", each
[ TimeStart = DateTime.Time([Start Time]),
TimeEnd = DateTime.Time([End Time]),
WokrkingMinutes = List.Times(TimeStart, if TimeEnd < TimeStart then Duration.TotalMinutes(#time(23, 59, 0) - TimeStart + TimeEnd - #time(0, 0, 0)) +2 else Duration.TotalMinutes(TimeEnd - TimeStart) +1, #duration(0,0,1,0)),
R_WorkingMinutes = List.Count(List.Intersect({MinutesList[R_Minutes], WokrkingMinutes})) -1,
O1_WorkingMinutes = List.Count(List.Intersect({MinutesList[O1_Minutes], WokrkingMinutes})) -1,
O2_WorkingMinutes = List.Count(List.Intersect({MinutesList[O2_Minutes], WokrkingMinutes})) -1
][[R_WorkingMinutes], [O1_WorkingMinutes], [O2_WorkingMinutes]], type record),
TransformWorkingMinutes = Table.TransformColumns(Ad_WorkingMinutes, {{"Working Minutes", each Table.SelectRows(Record.ToTable(_), (x)=> x[Value] > 0), type table}}),
ExpandedWorkingMinutesTable = Table.ExpandTableColumn(TransformWorkingMinutes, "Working Minutes", {"Name", "Value"}, {"Time Type", "Hours"}),
TransformColumns = Table.TransformColumns(ExpandedWorkingMinutesTable,
{ { "Time Type", each if Text.StartsWith(_, "R_") then "Regular" else if Text.StartsWith(_, "O1_") then "Overtime 50%" else "Overtime 100%", type text },
{ "Hours", each _ / 60, type number } } ),
Ad_Coefficient = Table.AddColumn(TransformColumns, "Coefficient", each if [Time Type] = "Regular" then 1 else if [Time Type] = "Overtime 50%" then 1.5 else 2, type number)
in
Ad_Coefficient