Adding Duration to time in previous row within the same currently calculated column

[Anonymous]

Hello Everybody,

Working on Power Query and ran into some obstacles

I would like to make the table add durations to the time in the previous rows

Currently my table looks like this:

Item No.        Duration(Hrs)   Start time

     1                     1                  09:00

     2                     2                  09:00          

     3                     3                  09:00

     4                     4                  09:00

The desired end result is:

Item No.        Duration(Hrs)   Start time      End Time

     1                     1                 09:00            10:00

     2                     2                 10:00            12:00          

     3                     3                 12:00            15:00

     4                     4                 15:00            19:00

Explanation:

I would like to track the start and end time of the item numbers within a process

Starting from Item 1, upon completion, Item 2 will start

I would like to add the duration to the previous end time and make the result the current start time of the item

Instead of adding mulitple indexed columns, is there a more efficient way of doing this?

Please help

Thank you very much in advanced

[ziying35]

@Anonymous 

The solution I provided is also implemented through the List.Generate function

// output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8ixJzVXwy9dTsjLUUXIpLUosyczP0/AoKtYEiwSXJBaVKJRk5qYqWRnoGZub1uog6zHC0GNEUI8xhh5jgnpMMPSYYNMTCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    chType = Table.TransformColumnTypes(Source,{{"Start time", type time}, {"Duration(Hrs)", type number}}),
    rows = List.Buffer(Table.ToRows(chType)),
    n = List.Count(rows),
    gen = List.Generate(
             ()=>{{}, 0}, 
             each _{1}<=n, 
             each let dur = #duration(0, rows{_{1}}{1}, 0, 0),
                      i = _{1}+1 
                  in if _{0}{3}?=null then
                        {rows{_{1}}&{rows{_{1}}{2}+dur}, i}
                     else {List.FirstN(rows{_{1}}, 2)&{_{0}{3}}&{_{0}{3}+dur}, i},
              each _{0}
          ),
    toTbl = Table.FromRows(List.Skip(gen), Table.ColumnNames(Source)&{"End Time"}),
    result = Table.TransformColumnTypes(toTbl,{{"Start time", type time}, {"End Time", type time}})
in
    result

There's a much more efficient way to write code using List.Generate function, and that's to use the double question mark syntax in the code(??). I've seen others use it, but I haven't really gotten the hang of it yet. Hopefully, one day, I'll be writing code using that syntax in the community to help people solve problems.

[Anonymous]

Hi @ziying35 

I read the definition of this operator and tryed this (actually i found a definition related to c #, but i think it's the same)

    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8ixJzVXwy9dTsjLUUXIpLUosyczP0/AoKtYEiwSXJBaVKJRk5qYqWRnoGZub1uog6zHC0GNEUI8xhh5jgnpMMPSYYNMTCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    chType = Table.TransformColumnTypes(Source,{{"Start time", type time}, {"Duration(Hrs)", type number}}),
    rows = List.Buffer(Table.ToRows(chType)),
    n = List.Count(rows),
    gen = List.Generate(
             ()=>{{}, 0}, 
             each _{1}<=n, 
             each let dur = #duration(0, rows{_{1}}{1}, 0, 0),
                      i = _{1}+1 
                  in {List.FirstN(rows{_{1}}, 2)&{_{0}{3}?}&{_{0}{3}?+dur}, i}??{rows{_{1}}&{rows{_{1}}{2}+dur}, i},
              each _{0}
          ),
    toTbl = Table.FromRows(List.Skip(gen), Table.ColumnNames(Source)&{"End Time"}),
    result = Table.TransformColumnTypes(toTbl,{{"Start time", type time}, {"End Time", type time}})
in
    result

getting this:

peraphs my PBI don't has this feature or I have to activate, but I don't know how or mo.re simply what I wrote is wrong

PS

understood, perhaps, where the problem lies. The list _ {0} is not null but only part of its elements are null.
Therefore ?? it is not triggered.

[ziying35]

@Anonymous 

This style of code should feel some operational efficiency improvements when the data volume is large.

[Anonymous]

@ziying35  where did you read about this operator?

My previous comment was related to a guess I made to change you code inside List.Generator in this way (I forgot to update the lines involved):

from this:

{List.FirstN(rows{_{1}}, 2)&{_{0}{3}?}&{_{0}{3}?+dur}, i}??{rows{_{1}}&{rows{_{1}}{2}+dur}, i},

ti this

{List.FirstN(rows{_{1}}, 2)&{_{0}{3}?}&{_{0}{3}?+dur}??rows{_{1}}&{rows{_{1}}{2}+dur}, i}

But the problem seems derive from the fact  that {null} in not equal to null.

From this I guess that this operator is mainly usefull for scalar values

In any case my proposal to the problem:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNLpVidaCUjIMsIzjMGsozhPBMgywTCiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t, dur = _t, start = _t]),
    ct = Table.TransformColumnTypes(Source,{{"item", Int64.Type}, {"dur", Int64.Type}, {"start", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ct, "end", each [start]+List.Sum(List.FirstN(ct[dur], List.PositionOf(ct[dur],[dur])+1))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"start"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "start", each [end]-[dur])
in
    #"Added Custom1"

PS

@ziying35  why do you use list instead record inside list.generate?

why

[ziying35]

@Anonymous 

I think I saw this code syntax article on Chris's blog, I can't remember exactly.It's a habit for me to use list instead of record in the code I write for the List.Generate function, and add some notes as appropriate.Depending on the problem scenario, I also write my code using the record method

PS:

The above information is translated via DeepL, hopefully you can understand the general idea, it may not be syntactically correct

[Anonymous]

here

@Smauro write about ?? operator

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can use List.Generate to achive this. Here the example with your data provided

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNLKwMDpVidaCUjIMcIWcAYyDFGFjABckzgArEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item No." = _t, Duration = _t, #"Start time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item No.", Int64.Type}, {"Duration", Int64.Type}, {"Start time", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Item No.", Order.Ascending}}),
    CreateStartEnd = List.Generate
    (
        ()=> [Counter = 0, Start= #"Sorted Rows"[Start time]{0}, End= #"Sorted Rows"[Start time]{0} + #duration(0,#"Sorted Rows"[Duration]{0},0,0)],
        each [Counter]<= Table.RowCount(#"Sorted Rows")-1,
        each 
        [
            Counter = [Counter]+1,
            Start = [End],
            End= [End]+ #duration(0,#"Sorted Rows"[Duration]{[Counter]+1},0,0)
        ],
        each {[Start], [End]}
    ),
    ToRows = Table.FromRows(CreateStartEnd, {"Start", "End"}),
    Combine = Table.FromColumns(Table.ToColumns(#"Sorted Rows")&Table.ToColumns(ToRows),Table.ColumnNames(#"Sorted Rows")&Table.ColumnNames(ToRows))

in
    Combine

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Greg_Deckler]

@ImkeF @edhans