Calculate Start and Finish date times along-with duration in hours for all weeks. A week starts on 6 AM of every Monday. The dates are in MDY format. So basically, you will need to split the rows into weeks where week starts on 6AM and finishes on 6AM after 7 days.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 123
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Split Time into Weekly Blocks with Power Query
Power Query solution 1 for Split Time into Weekly Blocks, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
T = each DateTime.From(Date.From(_)),
U = Duration.From,
S = Table.Combine(
Table.TransformRows(
Source,
each
let
s = [Start],
f = [Finish],
m = [Machine],
l = {s}
& List.Select(
List.DateTimes(T(s) + U(.25) + U(1), Duration.Days(T(f) - T(s)), U(1)),
(d) => d < f and Date.DayOfWeek(d) = 0
)
& {f}
in
Table.FromRows(
List.Transform(
List.Positions(List.RemoveLastN(l)),
each {m, l{_}, l{_ + 1}, Duration.TotalHours(l{_ + 1} - l{_})}
),
let
c = Table.ColumnNames(Source)
in
{c{2}, c{0}, c{1}} & {"Duration"}
)
)
)
in
SPower Query solution 2 for Split Time into Weekly Blocks, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Expand = Table.ExpandListColumn(Table.AddColumn(Source, "Days", each List.Numbers(Date.Day([Start]), Date.Day([Finish])-Date.Day([Start])+1)), "Days"),
Start = Table.AddColumn(Expand, "Date", each let
a = hashtag#date(Date.Year([Start]),Date.Month([Start]),[Days]),
b = if Date.From([Start]) = a then [Start] else if Date.From([Finish]) = a then [Finish] else a ,
c = if Date.DayOfWeek(b) = 0 then hashtag#datetime(Date.Year(b),Date.Month(b),Date.Day(b),6,0,0) else b in c),
Filter = Table.SelectRows(Start, each (Number.Mod( Number.From([Date]),1)>0)),
Index0 = Table.AddIndexColumn(Filter,"Idx0",0),
Index1 = Table.AddIndexColumn(Index0,"Idx1",1),
Merge = Table.NestedJoin(Index1, {"Machine", "Idx1"}, Index1, {"Machine", "Idx0"}, "Expanded Idx1", JoinKind.LeftOuter),
Remove = Table.SelectColumns(Merge,{"Machine", "Date", "Expanded Idx1"}),
Xpand = Table.ExpandTableColumn(Remove, "Expanded Idx1", {"Date"}, {"Finish"}),
Filter1 = Table.SelectRows(Xpand, each ([Finish] <> null)),
Rename = Table.RenameColumns(Filter1,{{"Date", "Start"}}),
Duration = Table.AddColumn(Rename, "Duration", each Number.Round( Number.From(([Finish]-[Start])*24),2))
in
Duration
Power Query solution 3 for Split Time into Weekly Blocks, proposed by Rick de Groot:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxBCoAwDAS/IjkLTXYrtPmDLyg9iAh68f9HC4LxOsNMa6KWtCQoOCkdlFls+ZDR8zLQuu3ndR8mfW4CDQ9HHR6MS3VaJHiTHEl1w/D8XYrnEgml9wc=", BinaryEncoding.Base64), Compression.Deflate)), {"Start", "Finish", "Machine"} ),
ChType = Table.TransformColumnTypes(Source, {{"Start", type datetime}, {"Finish", type datetime}}),
Times = Table.AddColumn(ChType, "Times", (x)=>
[a = List.Generate(
()=> [ Start = x[Start], Finish = List.Min({x[Finish], Date.EndOfWeek(x[Start], 1) + hashtag#duration(0,6,0,1)} )] ,
each Date.From( [Start]) <> Date.From( x[Finish] ),
each [ Start = [Finish],
Finish = let MyDuration = List.Min( { Duration.Days( x[Finish] - [Finish] ), 7 }) in
if MyDuration <> 7 then x[Finish] else Date.AddDays( [Finish], 7 )
] ) ,
b = Table.FromRecords(a)][b] ),
SelCols = Times[[Machine],[Times]],
ExpTable = Table.ExpandTableColumn(SelCols, "Times", {"Start", "Finish"}),
Duration = Table.AddColumn(ExpTable, "Duration", each Number.Round( Duration.TotalHours( [Finish] - [Start] ), 2 ) )
in
Duration
Power Query solution 4 for Split Time into Weekly Blocks, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Machines = Table.Group(
Source,
{"Machine"},
{
{
"All",
each
let
a = _,
b = Number.From(Date.From([Start]{0})),
c = Number.From(Date.From([Finish]{0})),
d = List.Transform({b .. c}, each DateTime.From(_ + .25)),
e = {[Start]{0}} & List.Select(d, each Date.DayOfWeek(_) = 1),
f = List.RemoveFirstN(e) & {[Finish]{0}},
g = List.Zip({e, f}),
h = List.Transform(g, each Duration.TotalHours(_{1} - _{0})),
i = Table.FromColumns({e, f, h}, List.RemoveLastN(Table.ColumnNames(a)) & {"Duration"})
in
i
}
}
),
Sol = Table.ExpandTableColumn(Machines, "All", Table.ColumnNames(Machines[All]{0}))
in
SolPower Query solution 5 for Split Time into Weekly Blocks, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = {Number.From(Date.From([Start])) .. Number.From(Date.From([Finish]))},
b = List.Transform(a, each {_} & {Date.WeekOfMonth(Date.From(_), 2)}),
c = List.Distinct(List.Transform(b, (x) => List.Select(b, each _{1} = x{1}))),
d = Table.Combine(
List.Transform(
c,
(x) =>
[
o = List.Transform(x, each _{0}),
p = DateTime.From(Text.From(Date.From(List.Min(o) - 1)) & " 06:00"),
q = DateTime.From(Text.From(Date.From(List.Max(o))) & " 06:00"),
r = Table.FromRows({{p} & {q}})
][r]
)
)
][d]
),
exp = Table.ExpandTableColumn(add, "Personalizar", Table.ColumnNames(add[Personalizar]{0})),
rec = Table.AddColumn(
exp,
"Personalizar",
each [
s =
if Number.From(Date.From([Start])) = Number.From(Date.From([Column1])) + 1 then
[Start]
else
[Column1],
f = if Date.From([Finish]) = Date.From([Column2]) then [Finish] else [Column2],
Duration = Duration.TotalHours(DateTime.From(f) - DateTime.From(s))
]
)[[Machine], [Personalizar]],
res = Table.ExpandRecordColumn(rec, "Personalizar", Record.FieldNames(rec[Personalizar]{0}))
in
resPower Query solution 6 for Split Time into Weekly Blocks, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name="tData123"]}[Content],
Transform = Table.TransformRows(Source, each let
_R = _,
_L = List.Generate( ()=>[s=_R[Start], r=[]], each [s]<=_R[Finish],
each let
_NextW = DateTime.From(DateTime.Date([s])) + hashtag#duration(7 - Date.DayOfWeek([s], Day.Monday),6,0,0),
_NextS = if (_NextW < _R[Finish]) then _NextW else _R[Finish],
_D = Duration.TotalHours(_NextS-[s]),
_NewR = [Machine=_R[Machine], Start=[s], Finish=_NextS, Duration=_D]
in [s=if ([s]<>_R[Finish]) then _NextS else _R[Finish]+hashtag#duration(0,0,0,1), r=_NewR],
each [r] )
in Table.FromRecords(List.Skip(_L)) ),
Combine = Table.Combine(Transform)
in
Combine
Power Query solution 7 for Split Time into Weekly Blocks, proposed by Luke Jarych:
let
FirstDayOfMonth = hashtag#datetime(Date.Year(start), Date.Month(start), 1, 6, 0, 0),
WeeksToAdd = Number.RoundDown(Duration.From(start - FirstDayOfMonth) / hashtag#duration(7, 0, 0, 0)) +1,
WeeksToAddNot0 = if WeeksToAdd = 0 then 1 else WeeksToAdd,
NextWeekStart = DateTime.From(FirstDayOfMonth - hashtag#duration(1, 0, 0, 0) + hashtag#duration(WeeksToAddNot0 * 7, 0, 0, 0)),
EndBiggerThanBoundaryWeek = NextWeekStart < end,
WeekRecords = List.Generate(
() => [WeekNum = 1, Machine = machine, Start = start, Finish = if EndBiggerThanBoundaryWeek then NextWeekStart else DateTime.From(Date.From(start) + hashtag#duration(WeekNum * 6, 0, 0, 0)) + hashtag#duration(0, 6, 0, 0)],
each [Start] < end,
each if [Finish] + hashtag#duration(7, 0, 0, 0) < DateTime.From(end) then
[Machine = machine, WeekNum = [WeekNum] + 1, Start = [Finish], Finish = [Finish] + hashtag#duration(7, 0, 0, 0)]
else
[Machine = machine, WeekNum = [WeekNum] + 1, Start = [Finish], Finish = DateTime.From(end)]
) in WeekRecords,
Power Query solution 9 for Split Time into Weekly Blocks, proposed by Obi E, MPH:
let Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content], #"Inse - Pastebin.com
Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.Solving the challenge of Split Time into Weekly Blocks with Excel
Excel solution 1 for Split Time into Weekly Blocks, proposed by Bo Rydobon 🇹🇭:
=REDUCE(HSTACK(G1,E1:F1,"Duration"),G2:G4,LAMBDA(a,v,LET(n,ROWS(G2:v),s,INDEX(E2:E4,n),f,INDEX(F2:F4,n),b,9/4,c,INT((s-b)/7),
w,SEQUENCE(INT((f+7-b)/7)-c,,c*7+b,7),d,IF(w>s,w,s),e,IF(w+7>f,f,w+7),
VSTACK(a,HSTACK(IF(w,v),d,e,(e-d)*24)))))Excel solution 2 for Split Time into Weekly Blocks, proposed by محمد حلمي:
=REDUCE(HSTACK(C1,A1:B1,"Duration"),A2:A4,
LAMBDA(a,d,LET(
r,OFFSET(d,,1),
w,OFFSET(d,,2),
x,SEQUENCE(r-d+1,,INT(d)),
v,FILTER(x,WEEKDAY(x)=2)+1/4,
i,VSTACK(d,v),m,VSTACK(v,r),
IFNA(VSTACK(a,HSTACK(w,i,m,24*(m-i))),w))))Excel solution 3 for Split Time into Weekly Blocks, proposed by 🇰🇷 Taeyong Shin:
=LET(
s, A2:A4, f, B2:B4,
w, WORKDAY.INTL(N(s) - 1, SEQUENCE(6), "0111111") + TIME(6, , ),
wt, FILTER(w, w <= MAX(f)),
sc, SORT(VSTACK(s, wt)), fc, SORT(VSTACK(f, wt)),
HSTACK(XLOOKUP(fc, s, C2:C4, , -1), sc, fc, (fc - sc) * 24)
)Excel solution 4 for Split Time into Weekly Blocks, proposed by Kris Jaganah:
=LET(p,E2:E4,q,F2:F4,r,G2:G4,s,--TEXTSPLIT(TEXTJOIN("#",,MAP(p,q,LAMBDA(x,y,LET(a,INT(x),b,VSTACK(x,SEQUENCE(y-a-1,,a+1),y),c,IF(WEEKDAY(b)=2,b+0.25,b),d,VSTACK(@c,DROP(c,-1)),e,SCAN(1,--(WEEKDAY(d)=2),LAMBDA(x,y,x+y)),f,UNIQUE(e),g,MAP(f,LAMBDA(z,ROUND(SUM((e=z)*(c-d)*24),2))),TEXTJOIN("#",,XLOOKUP(f,e,d)&"-"&XLOOKUP(f,e,c,,,-1)&"-"&g))))),"-","#"),VSTACK(HSTACK(G1,E1:F1,"Duration"),HSTACK(XLOOKUP(CHOOSECOLS(s,2),p,r,,-1),s)))Solving the challenge of Split Time into Weekly Blocks with Python in Excel
Python in Excel solution 1 for Split Time into Weekly Blocks, proposed by 🇰🇷 Taeyong Shin:
df = xl("A1:C4", headers=True)
s = df["Start"][0].date()
e = df["Finish"].max().date()
week_mon = pd.date_range(s, e, freq="W-Mon") + pd.DateOffset(hours=6)
start_df = (
pd.concat([week_mon.to_series(), df["Start"]])
.sort_values()
.reset_index(drop=True)
.to_frame(name="Start")
)
finish_df = (
pd.concat([week_mon.to_series(), df["Finish"]])
.sort_values()
.reset_index(drop=True)
.to_frame(name="Finish")
)
duration = (finish_df["Finish"] - start_df["Start"]).dt.total_seconds() / 3600
merged_s = pd.merge_asof(start_df, df.sort_values("Start"), on="Start", direction="backward")
merged_f = pd.merge_asof(finish_df, df.sort_values("Finish"), on="Finish", direction="forward")
pd.DataFrame(
{
"Machine": merged_s["Machine"],
"Start": start_df["Start"],
"Finish": merged_f["Finish"],
"Duration": duration,
}
).reset_index(drop=True)
&&&