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
ImkeF
3 years agoCommunity Champion
Hi dading ,
given the lack of clarification about what the transformations should achieve and taking into account my experiences with sparse data like this ("Oh, I forgot to include this and that") I decided not to follow this up.
So hopefully someone else will jump in here.
dading
3 years agoHelper II
ok , Sorry for the inconvenience
thank you.