how to add 15 min to datetime column in an accumulative way-CodePudding

I've an excel query from a table contains the below data and including (Start_Date/time) column. i need it create a new column and iterate the start_date to add 15 min in a new rows to each UserId and it's value as show below

Data to use:-

UserId  Duration    Start_Date  Duration2
x   27  04-09-22 5:30 AM    15
x   27  04-09-22 5:30 AM    12
x   26  04-09-22 7:45 AM    15
x   26  04-09-22 7:45 AM    11
x   24  05-09-22 7:30 AM    15
x   24  05-09-22 7:30 AM    9
x   18  06-09-22 3:45 AM    15
x   18  06-09-22 3:45 AM    3
x   32  06-09-22 7:30 AM    15
x   32  06-09-22 7:30 AM    15
x   32  06-09-22 7:30 AM    2

The Original Data:-

The Expected Data to see:-

CodePudding user response：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTIyBxIGJroGlrpGRgqmVsYGCo6 QCFDU6VYHQJKjBBKzJCVmFuZmGKagkuJIUKJCUiJKVwJNrfgUGIJV2FoAVJhBlVhjM0puJQYw1UYGyGrwOoSCpQAwy0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserId = _t, Duration = _t, Start_Date = _t, Duration2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserId", type text}, {"Duration", Int64.Type}, {"Start_Date", type datetime}, {"Duration2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UserId", "Duration"}, {{"All", each _, type table [UserId=nullable text, Duration=nullable number, Start_Date=nullable datetime, Duration2=nullable number]}}),
    #"Added to Column" = Table.TransformColumns(#"Grouped Rows", {{"All", each Table.AddIndexColumn(_,"Index",0,1) }}),
    Custom1 =   Table.TransformColumns(#"Added to Column", {{"All", each Table.AddColumn(_, "New_Start_Date", each _[Start_Date]    #duration(0,0, (15 * _[Index]),0))  }}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Duration2", "New_Start_Date"}, {"Duration2", "New_Start_Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"UserId", "Duration", "New_Start_Date", "Duration2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"New_Start_Date", type datetime}, {"Duration2", Int64.Type}})
in
    #"Changed Type1"