Hi Keith,
This is the updated version of the query. Could you please test and confirm that this is what you need?
let
Tasks =
let t = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVnJctNAEP0VSmcO0oxkqnxjTyCBmDXgOMHZg3G2C79PbFlMv96kSRUcTM2jt9evZ1rTafH37Pjqen7/pCqeFs8f/lbjsnz482Rvd/0jpB+hLGZPNUC1PnO7bH80/380Q86HdL7SAWFcJyfiuGySRzoAzsSNR208eghx49IGEHtjrqmFhmaskS69kFklFqK08FICaAzSwkuSVmlBieGVtEADEudfZ55/86jzDaksEE8HhA13NoDolvmt9IiYU6r2ltMfcmpYCNQC+qdbgDgjRdeWhUC5TbJqAhrgNgHoLsGZCEXRLdQ0MQ39MTIBjQ4w6oAdRgNqdAvNuE4WRmBOB8CZZ7TdlLRucS5B2ZWgtySXSFqjDLoFRErvIS411CW/0lut22CBpNU4D0JcuzFvy4arXY+2yTiQWVWS9C7XwvtcDdh5HCAYWVVc2uGFhhgUldmRwkfqoMyG3Uwp3uUhIFelRx+kgcoVyg9SKKPbDR+VQrtCuScBvoU96ZJPpT1eWwCYFmJp1E3GMJEx+HexiVI4d4ZOeCah0gqVJvwMqJLhEpyBy5giGpNCXMb8tE74fwoDS6HGClCDmELKLEDGhJvwMzCwjCyNaNAjiAGDDg//9ClHllaAzzlcSoBgSGuwLAwUvg6AlzzKRMMlbDKYXjpg8MV+BfiSoZTpPLS0zYwV4Cs34N0bOsBgWVoBvinMMC9LCVAZVDIsMOEjFmrdwuA7aAdgL7jgFvq7Mh5cC/u5WdqXdfD750euSz84lypnPCRAY1BD0YCf0iV7JiYAXE3st0ACwHig13Zxfprr0TQ3SdMi45WYAFTqYCTqACQ00ESPAa7OMIyMoGHdUAOVdJdqdMm+UiYA7QD6Q5GlqTgDr1gJODhQSj0EEQxyVDJPG0Rt0M9CcHp4hF0DIr7Y/bG4QURa7uBKzQYBD63GJfkaUVNEA1PPRsAj3HwiE0QGR9YI9gyv3M5YI0a0HKOebp1JVrkDe8ZpBHKgBDErxCOZFka3wGYXlTRp4ZCH0HclOJQsp+ySLh3lWjjiwhqclesK8EuWwV5XJgBYoPSzADBZKBV1AFutVW7bzXOz1ALgteVqx1xyL7h1mxfi7VS6PTrP5d6cCxgojfToWNbZvzad5Cb1RJDHec11AHYx63eJd/QACwPf7R2AKT59CUnAaaaKnXKyhZ6RcspL5e0GOwA+x2BWiPNnMgJ7850A2Dyu0p/xMEPPbaAF0MUVPHJ0QMS9gL2q6ABYWghItwCTuYFhIi2c5+rqeW6hz/mZ6KyZE6A2+k0p3IWMwb/4XUgJoKJpAUBYS1f2LhWX3Bv4pWC089mlAzC22uv+BKgN6VZa+io3hhYA1wY6HS0AjNzYC8B9YE/hfssY7K0uARjUUCr9W8bgPkgXuTrWAoIhxYpoLAq5NnbbYVGwz4KxZ8C1ABg/Za8FthCNrlK2gGAIn3Ue9qH+vGoBdKMJ40cHZD01FuszJQDc+fNHMsPfuP5RpNgl9zJX7JdKN7hBLzlZ+17US6WjXWYsC/5JGvrNAsAyJLhZulay5M6fa3GGBiQ9uuEG+q6sNzzz+AHPsgAj1F8A3XBdCT1Lh1tlOLgScCt10r8s3RZicWJ/aloB7hRldReod7J/KjfoO0lWqjLi/H1uR9+LjoGVi3qeP0F7DbBFub/RveftBt++FJ1sAbAZDAMA0J/2R/8O4K+iZv8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [webinar = _t, Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Table.Combine({t/*, t, t, t, t, t, t, t, */}),{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}})
in #"Changed Type",
Webinars =
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk9NysxLLFIwVNJRMrQyMAAihQBfIMcIwYnVQagzAkqZIqszQ1IXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"webinar name" = _t, #"start time" = _t, #"end time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"webinar name", type text}, {"start time", type time}, {"end time", type time}})
in
#"Changed Type",
#"Merged Queries" = Table.NestedJoin(Tasks, {"webinar"}, Webinars, {"webinar name"}, "Webinars", JoinKind.LeftOuter),
Main = Table.ExpandTableColumn(#"Merged Queries", "Webinars", {"start time", "end time"}, {"start time", "end time"}),
#"Added latestStartedAt" = Table.AddColumn(Main, "latestStartedAt", each List.Max({[startedAt],[start time]}), type time),
#"Added earliestFinishedAt" = Table.AddColumn(#"Added latestStartedAt", "earliestFinishedAt", each List.Min({[finishedAt], [end time]}),type time),
#"Filtered Rows" = Table.SelectRows(#"Added earliestFinishedAt", each [latestStartedAt] < [earliestFinishedAt]),
fCalculate = (t as table) =>
let
m = Table.Buffer(t),
fProcess = (a, n)=>
let
previousFinishedAt = List.Last(a)[earliestFinishedAt],
currentStartedAt = n[latestStartedAt],
currentFinishedAt = n[earliestFinishedAt],
actualFinish = List.Last(a)[actualFinish],
actualStart = List.Max({actualFinish, currentStartedAt}),
outputRecord = [#"ActualtimeSpent (minutes)" = List.Max({0, Duration.TotalMinutes(currentFinishedAt-actualStart)}), actualFinish = List.Max({actualFinish, currentFinishedAt})]
in outputRecord,
process = List.Skip(List.Accumulate(Table.ToRecords(m), {[actualFinish = m{0}[startedAt], #"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
in process,
Group = Table.Group(#"Filtered Rows", {"webinar", "Email"}, {{"Data", fCalculate}}),
Expand = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(Main, "ActualtimeSpent (minutes)", each null,type number))),
#"Removed Other Columns" = Table.SelectColumns(Expand,{"webinar", "Email", "startedAt", "finishedAt", "timeSpent (minutes)", "ActualtimeSpent (minutes)"}),
Output = Table.Combine({#"Removed Other Columns", Table.RemoveColumns(Table.SelectRows(#"Added earliestFinishedAt", each not ([latestStartedAt] < [earliestFinishedAt])), {"latestStartedAt", "earliestFinishedAt", "start time", "end time"})})
in
Output
Kind regards,
John
