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. ...
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!!
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!!