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:-
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"
