You need to fill in the blanks. Time In for blanks will be Time Out from non blank rows from the top sequentially. Once a row’s Time In is used, Time In from next non-blank row will be used. Time out will be Time In + Duration. Duration is in hours Ex. Item C – Time In from Item A will be used. Item E – Time In from Item B will be used. Item H – Time In from Item D will be used. Item J – Time In from Item F will be used.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 150
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Fill Missing Time Fields with Power Query
_x000D_Power Query solution 1 for Fill Missing Time Fields, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
R = each let T = Time.From(_) in Number.From(hashtag#time(Time.Hour(T), Time.Minute(T), 0)) + Number.From(Time.Second(T) > 29) / 1440,
F = each Table.SelectRows(Source, (r) => (r[Time In] = null) = _),
V = (_, O, C) => Table.ReplaceValue(_, null, O, (x, y, z) => z, C),
S = V(V(Source, each F(false){List.PositionOf(F(true)[Items], [Items])}[Time Out], {"Time In"}), each R([Time In] + [Duration] / 24), {"Time Out"})
in
S
Power Query solution 2 for Fill Missing Time Fields, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Dup = Table.DuplicateColumn(Source, "Time Out", "Time Out - Copy"),
BlankOrNot = Table.AddColumn(Dup, "Null or not", each if [Time In] = null then 0 else 1),
Group = Table.Group(BlankOrNot, {"Null or not"}, {{"All", each _, type table [Items=text, Time In=nullable number, Time Out=nullable number, Duration=number, #"Time Out - Copy"=nullable number, Null or not=number]}}),
Indx = Table.AddColumn(Group, "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
Remove = Table.RemoveColumns(Indx,{"Null or not", "All"}),
Xpand = Table.ExpandTableColumn(Remove, "Custom", {"Items", "Time In", "Time Out", "Duration", "Time Out - Copy", "Null or not", "Index"}, {"Items", "Time In", "Time Out", "Duration", "Time Out - Copy", "Null or not", "Index"}),
Sort = Table.Sort(Xpand,{{"Index", Order.Ascending},{"Items", Order.Descending}}),
FillUp = Table.FillUp(Sort,{"Time Out - Copy"}),
StageTimeIn = Table.AddColumn(FillUp, "In", each if [Time In] = null then [#"Time Out - Copy"] else [Time In]),
Remove1 = Table.RemoveColumns(StageTimeIn,{"Time In", "Time Out - Copy", "Null or not", "Index"}),
Power Query solution 3 for Fill Missing Time Fields, proposed by Kris Jaganah:
XType = Table.TransformColumnTypes(Remove1,{{"In", type time }, {"Time Out", type time}}),
Sort1 = Table.Sort(FillOut,{{"Items", Order.Ascending}}),
Remove2 = Table.RemoveColumns(Sort1,{"Time Out"}),
Rename = Table.RenameColumns(Remove2,{{"Custom" ,"Time Out"}, {"In", "Time In" }}),
ReOrder = Table.ReorderColumns(Rename,{"Items", "Time In", "Time Out", "Duration"})
in
ReOrder
Power Query solution 4 for Fill Missing Time Fields, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Time In", type time}, {"Time Out", type time}}),
NoNulls = Table.SelectRows(Type, each [Time In]<>null),
Out = List.FirstN(List.RemoveNulls(Type[Time Out]), Table.RowCount(Nulls)),
Nulls = Table.SelectRows(Type, each [Time In]=null)[[Items],[Duration]],
TimeIn = Table.FromColumns(Table.ToColumns(Nulls)&{Out}, Table.ColumnNames(Nulls)&{"Time In"}),
TimeOut = Table.AddColumn(TimeIn, "Time Out", each hashtag#duration(0,0,Int64.From([Duration]*60),0)+[Time In]),
Sol = Table.Sort(NoNulls&TimeOut, "Items")
in
Sol
Power Query solution 5 for Fill Missing Time Fields, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
ct = {List.FirstN(List.RemoveNulls(Fonte[Time Out]),List.Count(List.Select(Fonte[Time In], each _ = null)))},
gp = Table.Group(Fonte, {"Time In"}, {{"Contagem", each if [Time In]{0} = null then Table.RenameColumns(
Table.RemoveColumns(Table.AddColumn(Table.FromColumns(Table.ToColumns(_)&ct, Table.ColumnNames(Fonte)&{"a"} ), "b", each hashtag#duration(0,0,Number.Round([Duration]*60, 0),0) + Duration.From([a]) ),{"Time Out","Time In"}),{{"a","Time In"},{"b","Time Out"}}) else _}})[Contagem],
cmb = Table.Combine(gp),
tp = Table.TransformColumnTypes(cmb,{{"Time In", type duration}, {"Time Out", type duration}}),
res = Table.Sort(tp,{{"Items", 0}})
in
res
Power Query solution 6 for Fill Missing Time Fields, proposed by An Nguyen:
let
Raw= Excel.CurrentWorkbook () { [Name = "Raw" ] } [Content] ,
NewDuration = Table.AddColumn ( Raw , "New Duration" , each [Duration] / 24) ,
AsType = Table.TransformColumnTypes ( NewDuration , {
{ "Time In" , type time } ,
{ "Time Out" , type time } ,
{ "New Duration" , type duration}
}
) ,
AddIndexColumn = Table.AddIndexColumn ( AsType , "Index" , 1 ) ,
RunnningCountNull = Table.AddColumn ( AddIndexColumn , "IndexFillNull" ,
each List.Count(
List.PositionOf(
List.Range( AddIndexColumn [Time In] , 0 , [Index] ) ,
null , Occurrence.All )
) - 1
) ,
TimeOutNotNull = List.RemoveNulls ( AsType [Time Out] ) ,
NewTimeIn = Table.AddColumn ( RunnningCountNull , "Time In" , each if [Time In] = null then TimeOutNotNull{[IndexFillNull]} else [Time In]) ,
NewTimeOut = Table.AddColumn ( NewTimeIn , "Time Out" , each [Time In] + [New Duration] ) ,
Result = Table.SelectColumns ( NewTimeOut , {"Items" , "Time In" , "Time Out", "Duration" } )
in
Result
Power Query solution 7 for Fill Missing Time Fields, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData150"]}[Content],
LTO = List.Buffer(List.RemoveNulls(Source[Time Out])),
Transform = List.Accumulate(
Table.ToRecords(Source),
[r = {}, i = 0],
(s, c) =>
let
n =
if ((c[Time In] <> null)) then
c
else
Record.TransformFields(
c,
{{"Time In", each LTO{s[i]}}, {"Time Out", each LTO{s[i]} + c[Duration] / 24}}
)
in
[r = s[r] & {n}, i = s[i] + Number.From(c <> n)]
),
ToTable = Table.FromRecords(Transform[r])
in
ToTablePower Query solution 8 for Fill Missing Time Fields, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
C = Table.TransformColumnTypes(S,{{"Time In", type time}, {"Time Out", type time}}),
M = Table.TransformColumns(C, {{"Duration", each hashtag#duration(0,0,Number.Round(_*60, 0),0), type duration}}),
F = Table.SelectRows(M, each ([Time In] <> null)),
Tbl = Table.AddIndexColumn(F, "Index", 1, 1, Int64.Type),
C2 = M,
F2 = Table.SelectRows(C2, each ([Time In] = null)),
R = Table.SelectColumns(F2,{"Items", "Duration"}),
In = Table.AddIndexColumn(R, "Index", 0, 1, Int64.Type),
A = Table.AddColumn(In, "Time In", each Tbl[Time Out]{[Index]},type time),
A2 = Table.AddColumn(A, "Time Out", each [Time In]+[Duration], type time),
Tbl2 = Table.ReorderColumns(A2,{"Items", "Time In", "Time Out", "Duration", "Index"}),
C3 = Table.Combine({Tbl,Tbl2}),
S2 = Table.Sort(C3,{{"Items", Order.Ascending}}),
R2 = Table.SelectColumns(S2,{"Items", "Time In", "Time Out", "Duration"}),
Sol = Table.TransformColumns(R2,{{"Duration", Duration.TotalHours, type number}})
in
Sol
Power Query solution 9 for Fill Missing Time Fields, proposed by Peter Tholstrup:
let
source = [
_input = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
_types = {{"Time In", type time},{"Time Out", type time}},
_result = Table.TransformColumnTypes(_input, _types)
][_result],
keep = Table.SelectRows(source, each [Time In] <> null),
transform = Table.SelectRows(source, each [Time In] = null),
time_in = List.FirstN(keep[Time Out], Table.RowCount(transform)),
time_out = List.Transform(
List.Zip({time_in, transform[Duration]}),
each _{0} + hashtag#duration(0,0,Number.Round(_{1}*60,0),0)
),
transformed = hashtag#table(
Table.ColumnNames(source),
List.Zip({transform[Items], time_in, time_out, transform[Duration]})
),
result = Table.Sort(Table.Combine({keep, transformed}),"Items")
in
result
Power Query solution 10 for Fill Missing Time Fields, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"Time In", type time}, {"Time Out", type time}, {"Duration", Currency.Type}}),
FR = Table.SelectRows(#"Changed Type", each ([Time In] <> null)),
FR1 = Table.SelectRows(#"Changed Type", each ([Time In] = null)),
Prep = Table.FromColumns(
Table.ToColumns(FR)&{FR1[Items]&List.Repeat({null},List.Count(FR[Items])-List.Count(FR1[Items]))},
Table.ColumnNames(#"Changed Type")&{"Prv"}),
#"Merged Queries" = Table.NestedJoin(FR1, {"Items"}, Prep, {"Prv"}, "Prep", JoinKind.LeftOuter)[[Items],[Duration],[Prep]],
#"Expanded Prep" = Table.ExpandTableColumn(#"Merged Queries", "Prep", {"Time Out"}, {"Time In"}),
#"Added Custom" = Table.AddColumn(#"Expanded Prep", "Time Out", each [Time In] + hashtag#duration(0,0,Int64.From(60*[Duration]),0),type time),
#"Appended Query" = Table.Combine({FR, #"Added Custom"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Items", Order.Ascending}})
in
#"Sorted Rows"
Power Query solution 11 for Fill Missing Time Fields, proposed by Arden Nguyen, CPA:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = List.Buffer(List.RemoveNulls(Source[Time Out])),
b = List.Buffer(Table.SelectRows(Source, each [Time In] = null)[Items]),
c = Table.TransformColumnTypes(Source, {{"Time In", Time.Type}, {"Time Out", Time.Type}}),
d = Table.ReplaceValue(c,each [Time In], each [Time In] ?? a{List.PositionOf(b,[Items])},(v,o,n) as time => Time.From(n),{"Time In"}),
e = Table.ReplaceValue(d,each [Time Out], each [Time Out] ?? ( [Time In] + hashtag#duration(0,0,Byte.From(60*[Duration]),0) ),(v,o,n) as time => Time.From(n),{"Time Out"})
in
e
Solving the challenge of Fill Missing Time Fields with Excel
_x000D_Excel solution 1 for Fill Missing Time Fields, proposed by Bo Rydobon 🇹🇭:
=LET(z,B2:C11,d,D1:D11,
HSTACK(A1:A11,REDUCE(B1:C1,TAKE(z,,1),LAMBDA(a,v,LET(r,ROWS(v:B2),x,SORT(a),b,DROP(x,-1,1),t,LEFT(TEXT(INDEX(d,r+1),"00/24"),2)/24^2,
VSTACK(a,IF(v,INDEX(z,r,),XLOOKUP(TRUE,DROP(x,1,-1)-b>t,b,,1)+{0,1}*t))))),d))Excel solution 2 for Fill Missing Time Fields, proposed by Bo Rydobon 🇹🇭:
=LET(b,B2:B11,d,D2:D11,i,MAP(b,LAMBDA(v,IF(v,v,INDEX(TOCOL(C2:C11,3),COUNTIF(v:B2,""))))),HSTACK(A2:A11,i,i+d/24,d))Excel solution 3 for Fill Missing Time Fields, proposed by محمد حلمي:
=LET(b,B2:B11,d,D2:D11,i,LAMBDA(a,d,a+d),
c,IF(b,b,XLOOKUP(SCAN(0,b=0,i),SCAN(0,b>0,i),C2:C11)),
HSTACK(A2:A11,c,c+ROUND(d*60,)/1440,d))Excel solution 4 for Fill Missing Time Fields, proposed by Sunny Baggu:
=LET(
_a, FILTER(A2:D11, B2:B11 <> ""),
_b, FILTER(A2:D11, B2:B11 = ""),
_i, TAKE(_b, , 1),
_d, TAKE(_b, , -1),
_ti, TAKE(INDEX(_a, , 3), ROWS(_i)),
_to, _ti + ROUND(60 * _d, 0) / (60 * 24),
SORT(VSTACK(_a, HSTACK(_i, _ti, _to, _d)))
)Excel solution 5 for Fill Missing Time Fields, proposed by Charles Roldan:
=LET(ID, A2:A11, In, B2:B11, Out, C2:C11, Dur, D2:D11, Fill, --ISBLANK(In),
NewIn, In + Fill * INDEX(TOCOL(Out, 1),
Fill * SCAN(, Fill, LAMBDA(a, b, a + b))),
NewOut, ROUND((NewIn + Dur / 24) * 288, ) / 288,
HSTACK(ID, TEXT(NewIn, "h:mm"), TEXT(NewOut, "h:mm"), Dur))Excel solution 6 for Fill Missing Time Fields, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(j;(0.042)*D2:D11;i;LET(w;LAMBDA(a;b;a+b);q;IF(ISERROR(SEQUENCE(C2:C11<>"";;1;1));1;0);IF(C2:C11<>"";B2:B11;INDEX(FILTER(C2:C11;C2:C11<>"");IFERROR(IF(C2:C11<>"";SEQUENCE(COUNTA(C2:C11<>"");;1;1);"")-SCAN(0;q;w);0)+IF(q=0;0;SCAN(0;q;w)))));HSTACK(A2:A11;i;i+j;j))Excel solution 7 for Fill Missing Time Fields, proposed by Edwin Tisnado:
=LET(b,B2:B11,c,C2:C11,d,D2:D11,u,FILTER(c,c<>0),j,IF(b=0,SCAN(0,b,LAMBDA(x,y,IF(y=0,x+1,x+0)))),q,IF(j,INDEX(u,j),b),VSTACK(A1:D1,HSTACK(A2:A11,q,q+d/24,d)))Excel solution 8 for Fill Missing Time Fields, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(d,D2:D11,v,MAP(B2:B11,LAMBDA(x,IF(x="",SMALL(C2:C11,COUNTBLANK(B2:x)),x))),w,v+d/24,VSTACK(A1:D1,HSTACK(A2:A11,v,w,d)))Excel solution 9 for Fill Missing Time Fields, proposed by Luis Couto:
=LET(d,D2:D11,v,MAP(B2:B11,LAMBDA(x,IF(x="",SMALL(C2:C11,COUNTBLANK(B2:x)),x))),w,v+d/24,VSTACK(A1:D1,HSTACK(A2:A11,v,w,d)))
=LET(w,A2:A11,x,B2:B11,y,C2:C11,z,D2:D11,i,FILTER(SEQUENCE(ROWS(y)),y<>""),m,MAP(x,LAMBDA(t,LET(b,SUM(--(B2:t="")),IF(t="",INDEX(y,INDEX(i,b)),t)))),n,IF(y="",ROUNDUP(m+z/24,3),y),VSTACK(A1:D1,HSTACK(w,m,n,z)))Solving the challenge of Fill Missing Time Fields with Python
_x000D_Python solution 1 for Fill Missing Time Fields, proposed by Jan Willem Van Holst:
In Python:
import pandas as pd
from datetime import datetime, timedelta
df = pd.read_csv(r"C:JWLENOVOPYTHONPower_Query_Challenge_150.csv", sep=';', usecols=[0,1,2,3])
df['Duration'] = df['Duration'].str.replace(',','.').astype(float)
remain= df.dropna().to_numpy().tolist()
workingList = df.fillna('').to_numpy().tolist()
for row in workingList:
if row[1]=='':
row[1] = remain[0][2]
del remain[0]
time = datetime.strptime(row[1], '%H:%M')
minutesToAdd = row[3]*60
newtime = pd.Timestamp(time + timedelta(minutes=minutesToAdd)).round(freq='min')
newtimeString = newtime.strftime('%H:%M')
# print(row[0], row[1], time, minutesToAdd, newtime, newtimeString)
row[2]=n&ewtimeString
answer = workingList
Solving the challenge of Fill Missing Time Fields with R
_x000D_R solution 1 for Fill Missing Time Fields, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(lubridate)
library(hms)
input = read_excel("Power Query/PQ_Challenge_150.xlsx", range = "A1:D11") %>%
janitor::clean_names()
test = read_excel("Power Query/PQ_Challenge_150.xlsx", range = "F1:I11") %>%
janitor::clean_names() %>%
mutate(across(c(time_in, time_out), ~as_hms(.x)))
result = input %>%
mutate(across(c(time_in, time_out), ~as_hms(.x))) %>%
group_by(empty = is.na(time_in)) %>%
mutate(nr = row_number()) %>%
ungroup() %>%
group_by(nr) %>%
mutate(time_in = if_else(empty, first(time_out), time_in),
time_out = if_else(empty, time_in + dminutes(round(duration * 60,0)), time_out)) %>%
ungroup() %>%
select(-c(empty, nr))