Forum Discussion

kwidhiya's avatar
kwidhiya
New Member
5 years ago
Solved

How to Create and Add Row between dates and cut the value by daily hours

Hi All,

 

I really need all of your help for this one,

I have sample data like this

I want to expand between the Start_Time and End_Time and re-calculate the duration by the day,

What i want is like this data below

For your additional information,

My cut off time for daily is every 06:00:00 AM

 

Thanks for your help, i love this community and if you need any information just ask me!

 

Thank You!

Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    5 years ago

    kwidhiya 

    I create a column with flexibility for any start and end time, please check and update here.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNbDUNTJU0lEKyk9PLQLS4flF2UAKJqNgaWVgAOKbQPkWVsYgvom5nqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Status = _t, #"Start Time" = _t, #"End Time" = _t, #"Duration (Hours)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Status", type text}, {"Start Time", type datetime}, {"End Time", type datetime}, {"Duration (Hours)", type number}},"en-gb"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let

    s = [Start Time],
    e = [End Time],
    v1 = Date.StartOfDay([Start Time]) + #duration(0,6,0,0),
    v2 = if s < v1 then v1 else Date.StartOfDay([Start Time]) + #duration(1,6,0,0),
    Final = 
         
      List.Combine(
        {
            List.Generate(
                    ()=>  v2,
                    each _ < e,
                    each _ + #duration(0,24,0,0)
                )
            ,
            {e}
        }  
    )
in
Table.AddColumn(
    Table.SelectRows(   
        Table.FromColumns( { List.Combine( { {s} , List.Skip(Final,0)} ),List.Skip(Final,0)}, {"Start","End"} ),
        each _[End] <> null
    ),
    "Duration",
    each Number.From(([End] - [Start])*24)  
)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"End", "Duration"}, {"End", "Duration"})
in
    #"Expanded Custom"

     

     

     

     The file is attached below my signature.

     

3 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    5 years ago

    HI kwidhiya , 

    Give this Power Query column a try:

    let 
StartDown = Number.RoundDown(Number.From([Start_Time])) + 0.25,
EndDown = Number.RoundDown(Number.From([End_Time])) + 0.25,
Start = Number.From([Start_Time]), 
End = Number.From([End_Time]),
Addition1 = if Start<StartDown then 1 else 0,
Addition2 = if End>EndDown then 1 else 0
in
List.Transform(
List.Numbers( 
    StartDown , EndDown - StartDown + Addition1 + Addition2 ) , 
    each 
    if _ = StartDown then 
    if Start < StartDown then (StartDown - Start) * 24
    else ((StartDown+1) - Start) * 24

    else if _ = EndDown then 
    if End > EndDown then ((End - EndDown) * 24)
    else 24 - ((EndDown - End) * 24)

    else 24
    )


    Br, 
    Johannes

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    kwidhiya 

    I create a column with flexibility for any start and end time, please check and update here.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNbDUNTJU0lEKyk9PLQLS4flF2UAKJqNgaWVgAOKbQPkWVsYgvom5nqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Status = _t, #"Start Time" = _t, #"End Time" = _t, #"Duration (Hours)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Status", type text}, {"Start Time", type datetime}, {"End Time", type datetime}, {"Duration (Hours)", type number}},"en-gb"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let

    s = [Start Time],
    e = [End Time],
    v1 = Date.StartOfDay([Start Time]) + #duration(0,6,0,0),
    v2 = if s < v1 then v1 else Date.StartOfDay([Start Time]) + #duration(1,6,0,0),
    Final = 
         
      List.Combine(
        {
            List.Generate(
                    ()=>  v2,
                    each _ < e,
                    each _ + #duration(0,24,0,0)
                )
            ,
            {e}
        }  
    )
in
Table.AddColumn(
    Table.SelectRows(   
        Table.FromColumns( { List.Combine( { {s} , List.Skip(Final,0)} ),List.Skip(Final,0)}, {"Start","End"} ),
        each _[End] <> null
    ),
    "Duration",
    each Number.From(([End] - [Start])*24)  
)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"End", "Duration"}, {"End", "Duration"})
in
    #"Expanded Custom"

     

     

     

     The file is attached below my signature.