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 result

 

link file 

 

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_Daniel's avatar
    wdx223_Daniel
    Community 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's avatar
      dading
      Helper II

      The result is exactly what I wanted. 

      i also have add more rows data and it works

       

      thank you.

  • ImkeF's avatar
    ImkeF
    Community Champion

    Hi dading ,
    getting this error-message when trying to download the data:

     

  • ImkeF's avatar
    ImkeF
    Community 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's avatar
      dading
      Helper II

      yes you are correct, cycle could be more than one row.

      i have add a cycle to make it clear

  • ImkeF's avatar
    ImkeF
    Community 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's avatar
      dading
      Helper II

      ok , Sorry for the inconvenience

       

      thank you.