Forum Discussion
dading
3 years agoHelper II
Merge status and cycle
Hi everyone, i'm having trouble merging 2 tables base on id and timestamp could you please help to solve it using power query? i attach the file in link below. the result i want is in sheet res...
- 3 years ago
let status = Excel.CurrentWorkbook(){[Name="status"]}[Content], cycle = Excel.CurrentWorkbook(){[Name="cycle"]}[Content], Custom1 = let cyclegrp=Table.Group(cycle,"Truck",{"n",each Table.Sort(_,"Timefull")}) in #table( Table.ColumnNames(status)&{"Remark"}, List.TransformMany( Table.ToRows(status), each let a=cyclegrp{[Truck=_{0}]}?[n]?, b=Table.FirstN(a,(x)=>x[Timefull]<=_{1}), c=Table.FirstN(Table.Skip(a,Table.RowCount(b)),(x)=>x[Timefull]<_{2}), d={List.Max({List.Last(b[Timefull],null),_{1}})}&c[Timefull]&{_{2}}, e={List.Last(b[Remark],null)}&c[Remark] in if a=null then {{_{1},_{2},null}} else List.Zip({List.RemoveLastN(d),List.Skip(d),e}), (x,y)=>{x{0},y{0},y{1},Duration.TotalHours(y{1}-y{0}),x{4},y{2}} ) ) in Custom1
wdx223_Daniel
3 years agoCommunity Champion
let
status = Excel.CurrentWorkbook(){[Name="status"]}[Content],
cycle = Excel.CurrentWorkbook(){[Name="cycle"]}[Content],
Custom1 = let
cyclegrp=Table.Group(cycle,"Truck",{"n",each Table.Sort(_,"Timefull")})
in
#table(
Table.ColumnNames(status)&{"Remark"},
List.TransformMany(
Table.ToRows(status),
each let
a=cyclegrp{[Truck=_{0}]}?[n]?,
b=Table.FirstN(a,(x)=>x[Timefull]<=_{1}),
c=Table.FirstN(Table.Skip(a,Table.RowCount(b)),(x)=>x[Timefull]<_{2}),
d={List.Max({List.Last(b[Timefull],null),_{1}})}&c[Timefull]&{_{2}},
e={List.Last(b[Remark],null)}&c[Remark]
in
if a=null then {{_{1},_{2},null}}
else List.Zip({List.RemoveLastN(d),List.Skip(d),e}),
(x,y)=>{x{0},y{0},y{1},Duration.TotalHours(y{1}-y{0}),x{4},y{2}}
)
)
in
Custom1
dading
3 years agoHelper II
The result is exactly what I wanted.
i also have add more rows data and it works
thank you.