How to substract 30 min from a value the set the rest of value in a new raws-CodePudding

I've a query in excel contains the below data columns, i need to find a way to iterate the below logic

if the duration > 30, then subtract 30 min from the value then generate new raw with the rest of the value and do this for all status and also add 30 min to the Start_date and End_Date time

Example:-

UserId                    Status          Duration       Start_Date       End_Date

Eman.Aldosary   working on email, no acd     552         06-09-22 7:30  06-09-22 16:30

The Expected Data:-

CodePudding user response：

Below, in powerquery

Groups on UserID and Status, then processes as seems to be requested

Main query

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"Status", type text}, {"Duration", Int64.Type}, {"Start_Date", type datetime}, {"End_Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"UserID", "Status"}, {{"data", each Process(_), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", { "Custom", "Start_Date2", "End_Date2","Duration2" }, {"Duration","Start_Date", "End_Date","Countdown" }),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded data",{{"Start_Date", type datetime}, {"End_Date", type datetime}})
in  #"Changed Type1"

function Process

(Table as table) =>
let  #"Changed Type" = Table.TransformColumnTypes(Table,{{"UserID", type text}, {"Status", type text}, {"Duration", Int64.Type}, {"Start_Date", type datetime}, {"End_Date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration2", each  List.Combine({List.Generate(() => [Duration], each _ >= 0, each  _-30 ),{0}})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Duration2"),
#"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Start_Date2", each [Start_Date] #duration(0,0,30*[Index],0)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "End_Date2", each [End_Date] #duration(0,0,30*[Index],0)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each if [Duration2]=0 then [Duration]-30*([Index]-1) else 30),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Duration", "Start_Date", "End_Date", "Index"})
in  #"Removed Columns"

Looking forward to Ron's List.Generate/List.Accumulate version that has perhaps 5 rows of code