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

[kwidhiya]

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!

[Fowmy · Accepted solution]

@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.

[tex628]

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

[AllisonKennedy]

@kwidhiya 

Using this method: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html

See attached file below signature. 

I added a 'Start of Day' and 'End of Day' columns to the DimDate table, then it should work with your current data model if the date table is UNRELATED to the Hours table.