Forum Discussion

dading's avatar
dading
Helper II
3 years ago
Solved

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...
  • wdx223_Daniel's avatar
    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