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
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)
- R = Regular
- O1 = Overtime 50%
- O2 = Overtime 100%
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 @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
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
