Merge status and cycle

[dading]

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

[wdx223_Daniel · Accepted solution]

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]

The result is exactly what I wanted. 

i also have add more rows data and it works

thank you.

[ImkeF]

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]

ok , Sorry for the inconvenience

thank you.

[ImkeF]

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]

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

i have add a cycle to make it clear

[ImkeF]

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

[dading]

please re-try

link