Loop

[2025cindy]

I would like to use loop to obtain a table as follows,

00 4
01 9
02 9
03 9
04 8

At 00 Clock, total count is 4 as Discharge Date as no patient Discharge Date is equal to 00. 4 - 0 = 4

At 01 Clock, total count is 9 as there is 0 patient EDIS Discharge Date is <= to 01,
9 - 0 = 9

At 02 Clock, total count is 9 as there is 1 patient who's EDIS Discharge Date is <= 02.
10 - 1 = 9

At 03 Clock, total count is 9 as there is 2 patients who's EDIS Discharge Date is <= 03.
11 - 2 = 9

At 04 Clock, total count is 9 as there is 5 patients who's EDIS Discharge Date is <=04.
13 - 5 = 8

discharge date update frequently, some discharge date is null before update.

[wdx223_Daniel · Accepted solution]

NewStep = #table(
                     {"Clock","Count"},
                     Record.ToList(
                                   List.Accumulate(
                                                   Table.ToRecords(PreviousStepName),
                                                   {[],List.Repeat({0},25),1},
                                                   (x,y)=>let
                                                             a=Time.Hour(y[EDIS Discharge Date])??24,
                                                             b=List.ReplaceRange(x{1},a,1,{x{1}{a}+1}),
                                                             c=Record.TransformFields(x{0},{y[Clock],each {y[Clock],x{2}-List.Sum(List.FirstN(b,Number.From(y[Clock])+1))}},2)
                                                          in
                                                            {c,b,x{2}+1}
                                                  ){0}
                                  )
                    )