Calculate Duration.Days Using Dynamically Expanding Date Columns

Thank you wdx223_Daniel,

Your solution to create the summary Revision and Review columns was instructional for me, thank you! I am able to recreate using your code (copied below, if helpful to others).  However, what I am needing specifically (along with the summaries) are outputs of the individual 'buckets' of days in either revision or review action status. This is why I had begun with assigning an index and then pivoting to distinguish between sequences of 'Resubmit-Require Revisions' and 'Require Revisions-Resubmit'. Can your solution be modified to also allow for this? I am still learning and this is still beyond my grasp!!

From my original comment above, here are the action columns which I would need (from the dynamically expanding data) which I would then sum:

Duration.Days (in Revision) Columns Needed:

Resubmit.1-Require Revisions.1

Resubmit.2-Require Revisions.2

Resubmit.3-Require Revisions.3

Duration.Days (in Review) Columns Needed:

Require Revisions.1-submit

Require Revisions.2-Resubmit.1

Require Revisions.3-Resubmit.2

(formatted solution to create 'summary' revision and review columns from wdx223_Daniel)

= Table.FromRecords(
Table.Group(
#"Filtered Rows",
{"recordNumber", "submissionId"},
{
"n",
each
let
a = Table.Sort(_, "actionDate"),
b = List.Accumulate(
a[actionDate],
{},
(x, y) => if x = {} then {{}, y} else {x{0} & {Duration.Days(y - x{1})}, y}
){0}
in
[
recordNumber = [recordNumber]{0},
submissionId = [submissionId]{0},
#"Total Time in Review" = List.Sum(List.Alternate(b, 1, 1, 1)) ?? 0,
#"Total Time in Revision" = List.Sum(b) - #"Total Time in Review",
#"Total Time" = List.Sum(b)
]
}
)[n]
)