Calculate Duration.Days Using Dynamically Expanding Date Columns

[Anonymous]

I have a large dataset containing form submission action/action dates. I am working to calculate total time in revision and review. Specifically, an itemization of 'time in revision' (with user) vs. 'time in review' (with office) on each submission. Because a record can have multiple submissions and each submission typically has multiple actions taken, pivoting on actionType to create distinct action columns with dates to use with Duration.Days shoots an error.

I was able to resolve the pivot error by first grouping on recordId/actionType, adding a nested Table.AddIndexColumn, then merging actionType with Index. The pivot then successfully creates distinct (sequenced) actions columns with dates for each submission (whatever the number of actions) which I planned to use to calculate Duration.Day in the M query. However, I now realize I do not know how to write the Duration.Days calculation in M to dynamically to calculate the time between each (pivoted) action column's date when the number of action date columns is fluid.

Here is an example of the base data:

recordNumber	submissionId	actionType	actionDate
832	63221691f7cc2c003c434531	Submit	2021-07-14T14:11:49.3100000
832	63221691f7cc2c003c434531	Approve	2021-07-23T16:54:11.0250000
832	63221691f7cc2c003c434640	Submit	2022-08-14T14:11:49.3100000
832	63221691f7cc2c003c434640	Require Revisions	2022-08-26T16:54:11.0250000
832	63221691f7cc2c003c434640	Resubmit	2022-08-27T16:55:30.4540000
832	63221691f7cc2c003c434640	Require Revisions	2022-08-28T16:56:42.3630000
832	63221691f7cc2c003c434640	Resubmit	2022-08-28T16:58:11.0980000
832	63221691f7cc2c003c434640	Require Revisions	2022-08-29T16:59:19.4700000
832	63221691f7cc2c003c434640	Resubmit	2022-08-30T17:00:48.1880000
832	63221691f7cc2c003c434640	Approve	2022-09-22T17:02:13.4390000

And here is the Grouped/Nested data I created in potential solution to sequence actions (merging the actionType and Index new column):

recordNumber	submissionId	actionTypeIndexed	actionDate
832	63221691f7cc2c003c434640	Submit.1	2022-08-14T14:11:49.3100000
832	63221691f7cc2c003c434640	Require Revisions.1	2022-08-26T16:54:11.0250000
832	63221691f7cc2c003c434640	Require Revisions.2	2022-08-28T16:56:42.3630000
832	63221691f7cc2c003c434640	Require Revisions.3	2022-08-29T16:59:19.4700000
832	63221691f7cc2c003c434640	Resubmit.1	2022-08-27T16:55:30.4540000
832	63221691f7cc2c003c434640	Resubmit.2	2022-08-28T16:58:11.0980000
832	63221691f7cc2c003c434640	Resubmit.3	2022-08-30T17:00:48.1880000
832	63221691f7cc2c003c434640	Approve.1	2022-09-22T17:02:13.4390000

Submit and Approve actions will always be Submit.1 and Approve.1. There are only duplicate actions for Require Revisions or Resubmit actions.

Pivoting on 'actionTypeIndexed' now dynamically creates a column for any number of actions on a submission. Example:

recordNumber	submissionId	Submit	Require Revisions.1	Require Revisions.2	Require Revisions.3	Resubmit.1	Resubmit.2	Resubmit.3	Approve
832	63221691f7cc2c003c434640	2022-08-14T14:11:49.3100000	2022-08-26T16:54:11.0250000	2022-08-28T16:56:42.3630000	2022-08-29T16:59:19.4700000	2022-08-27T16:55:30.4540000	2022-08-28T16:58:11.0980000	2022-08-30T17:00:48.1880000	2022-09-22T17:02:13.4390000

However, my issue is when I attempt to use Duration.Days to find 'time in revision' and 'time in review' number of days as I need , I cannot hard code the Duration.Days calculations - since I don' t know how many sequences of Require Revisions and Resubmit columns there may be.  Here is what I am going for:

		Duration.Days (in Revision)	Total Time in Revision	Duration.Days (In Review)
recordNumber	recordId	Resubmit.1-Require Revisions.1	Resubmit.2-Require Revisions.2	Resubmit.3-Require Revisions.3	Require Revisions.1-submit	Require Revisions.2-Resubmit.1	Require Revisions.3-Resubmit.2	Approve-Resubmit.3	Total Time in Review	TOTAL TIME
832	63221691f7cc2c003c434640	1	0	1	2	12	1	1	23	37	39

Could anyone provide a potential solution or idea on how to calculate Duration.Days using dynamically expanding date columns? Or, potentially share a better method to tackling this issue? I am open to expanding what I have already done or scrapping for a 'best' method - whichever works.  Thanks!

[wdx223_Daniel]

can this help?

[Anonymous]

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]
)

[jbwtp]

Hi @Anonymous,

are you trying to achieve something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdI5DsIwEEDRq6DUxJrN23RcIaRDNERGSsEayPlJnIalYXNlydLTH41XqyIwFfPCMRG6iFvfNNQAcCMslnF4Wl43u/YyXAgIS/AlSo2iiCrRMMJ4ivX8DWlxPJ4PfbqjiGt0akfNANl3KCfwFEUlhG+iJqlKp2t7TrMq9W3XHvbdHUruy7wqdc+B5LNllcGIlT8Fhow6FTLs+LfAyQp52Bj+FBgzGhWjEf/RWl4CGWr0CqASDIYPAh/+3UDFkihTpMhGOE7U+gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [recordNumber = _t, submissionId = _t, actionTypeIndexed = _t, actionDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"recordNumber", Int64.Type}, {"submissionId", type text}, {"actionTypeIndexed", type text}, {"actionDate", type datetime}}),
    f = (t as table) as table => Table.FromRecords(List.Skip(List.Accumulate(Table.ToRecords(t), {[Iteration = 0, Start = #date(2000,1,1)]}, (a, n)=> if n[actionTypeIndexed] = "Require Revisions" then a & {[Iteration = List.Last(a)[Iteration]+1, Start = n[actionDate]]} else if n[actionTypeIndexed] = "Resubmit" then List.RemoveLastN(a, 1) & {Record.AddField(List.Last(a), "Stop", n[actionDate])} else a))),
    Grouped = Table.Group(#"Changed Type", {"recordNumber", "submissionId"}, {{"Data", f}}),
    #"Expanded Data" = Table.ExpandTableColumn(Grouped, "Data", {"Iteration", "Start", "Stop"}, {"Iteration", "Start", "Stop"})
    
in
    #"Expanded Data"

Kind regasd,

John

[Anonymous · Accepted solution]

Thank you, John:

Your solution seems to work well for what I was needing here. I was able to recreate using your example M. I had to modify to adjust for the last Resubmit (which goes to Approve and not another Require Revisions), but I added an OR and that was fairly simple. I appreciate your help!!