Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

8 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        ziying35
        Impactful Individual
        6 years ago

        Anonymous 

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