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 ,
I must admit that I am a bit surprised that you didn't give any explanation to this transformation, as it doesn't look trivial to me.
Please consider explaining what you are actually trying to achieve here.
Specifically I have the following question: In your sample data there is always only one entry from table "cycle" for each row of the table "status" at max. Will this always be the case or could there be more than one row from table "cycle" for a single row of table "status"?
dading
3 years agoHelper II
yes you are correct, cycle could be more than one row.
i have add a cycle to make it clear