Forum Discussion
Calculate Duration.Days Using Dynamically Expanding Date Columns
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!
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!!
4 Replies
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
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!!
can this help?
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]
)
