Forum Discussion
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 result
thank you
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
8 Replies
- wdx223_DanielCommunity 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- dadingHelper II
The result is exactly what I wanted.
i also have add more rows data and it works
thank you.
- ImkeFCommunity Champion
- ImkeFCommunity 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"?- dadingHelper II
yes you are correct, cycle could be more than one row.
i have add a cycle to make it clear
- dadingHelper II
ok , Sorry for the inconvenience
thank you.