how to generate a new rows and adding 1 min after subtract two datetimes from each other-CodePudding

i've a data as a table and trying by excel query to subtract the two columns of datetime(end_time - Start_time) and generate new rows between them and add 1 min to every new row.

Data Sample:-

UserId  Status  Duration    Start Date  End Date
a.hassan    gone home   15  21-09-22 16:36  21-09-22 16:51

The Expected Data to see:-

UserId  Status  Duration    Start Date  End Date
a.hassan    gone home   15  21-09-22 16:36  21-09-22 16:37
a.hassan    gone home   15  21-09-22 16:37  21-09-22 16:38
a.hassan    gone home   15  21-09-22 16:38  21-09-22 16:39
a.hassan    gone home   15  21-09-22 16:39  21-09-22 16:40
a.hassan    gone home   15  21-09-22 16:40  21-09-22 16:41
a.hassan    gone home   15  21-09-22 16:41  21-09-22 16:42
a.hassan    gone home   15  21-09-22 16:42  21-09-22 16:43
a.hassan    gone home   15  21-09-22 16:43  21-09-22 16:44
a.hassan    gone home   15  21-09-22 16:44  21-09-22 16:45
a.hassan    gone home   15  21-09-22 16:45  21-09-22 16:46
a.hassan    gone home   15  21-09-22 16:46  21-09-22 16:47
a.hassan    gone home   15  21-09-22 16:47  21-09-22 16:48
a.hassan    gone home   15  21-09-22 16:48  21-09-22 16:49
a.hassan    gone home   15  21-09-22 16:49  21-09-22 16:50
a.hassan    gone home   15  21-09-22 16:50  21-09-22 16:51

CodePudding user response：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"Status", type text}, {"Duration", Int64.Type}, {"Start", type datetime}, {"End", type datetime}}),
Minute = 1/24/60,
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Index", each let end =  Number.From([End]) in List.Positions(List.Generate(() => Number.From([Start]), each _ <=  end, each _   Minute))),
#"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Index"),
#"Added Custom" = Table.AddColumn(#"Expanded Custom.2", "Start.2", each Number.From([Start]) [Index]*Minute),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "End.2", each [Start.2] Minute),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Start.2", type datetime}, {"End.2", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Start", "End",  "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Start.2", "Start"}, {"End.2", "End"}})
in  #"Renamed Columns"

Undoubtedly, next question will be " I have more rows. Now what?" Convert this to a function then apply to all the rows

CodePudding user response：

Here's another method, also using Power Query. This method uses List.Accumulate to create a list of records for a single row, where the Start and End times are incremented by one minute Duration times.

It then uses List.Generate to create a List of the previously generated Records for each row.

let
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"UserId", type text}, {"Status", type text}, {"Duration", Int64.Type}, 
        {"Start Date & Time", type datetime}, {"End Date & Time", type datetime}}),

    #"Added Rows" = Table.FromRecords(List.Combine(
        List.Generate(
            ()=>[r=List.Accumulate({1..#"Changed Type"[Duration]{0}},{},(state, current)=>
                state & {Record.Combine({
                            Record.RemoveFields(#"Changed Type"{0}, {"Start Date & Time", "End Date & Time"}),
                            [#"Start Date & Time"=#"Changed Type"[#"Start Date & Time"]{0}   #duration(0,0,current-1,0)],
                            [#"End Date & Time"=#"Changed Type"[#"Start Date & Time"]{0}   #duration(0,0,current,0)]})}), 
                idx=0],
            each [idx] < Table.RowCount(#"Changed Type"),
            each [r=List.Accumulate({1..#"Changed Type"[Duration]{[idx] 1}},{},(state, current)=>
                    state & {Record.Combine({
                        Record.RemoveFields(#"Changed Type"{[idx] 1}, {"Start Date & Time", "End Date & Time"}),
                        [#"Start Date & Time"=#"Changed Type"[#"Start Date & Time"]{[idx] 1}   #duration(0,0,current-1,0)],
                        [#"End Date & Time"=#"Changed Type"[#"Start Date & Time"]{[idx] 1}   #duration(0,0,current,0)]})}), 
                    idx=[idx] 1],
            each [r])), type table[UserId=text, Duration=Int64.Type, #"Start Date & Time"=datetime, #"End Date & Time"=datetime])
in
    #"Added Rows"

I have no idea as to whether this is more or less efficient than your other answer.