Hi @mark_carlisle,
I'm still thinking how to improve the performance. I've implemented another way without recursion, check it out and please test with your big dataset.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdRLi9swEADgv2JyXoI0oxk9bmVpYQ+F0O1tyUGkJoT6EWynNP++kh27cjEFxweLkR/yx2hGHx87REGW2Oxedp9/+/Ja5NlrXV59dQ93rNFMCiGEr7e2q8u8CaFQewl7EDJ+JNmBcMAhPPh7UxdFiL59fwvjl8N7GDX0rz2uYTaM06+PL9FhgaTSaxy4FxQdNk6sI+HQzhxvVdv5ovDdpa4yX/3IPp26y69++vdpdmjqc+PL6EKKTjGCEclMtyfhyDUK6FmuVE6i61d/nmuG1EYuJFxJjIteQ3KDV7ET27wK7OjF1GstLHmt6X/9nJdjfkFs8kqgqR5UAkbo6/RfMAoBsAEMNuR4E5hYjl5KvUaYRS8hPu3VToV+09sSbAlGMCdg0iyWwKQ1rzuoEjA5AkdqBn7Pu9u1l371lT/ncfGwdH3PYwiCR51OdZZ5ScfKruovNd9+Mk7hTBcSdcrb9lKd45d10+QhndU581l5Can8mfcKmprKJEiLsHRoKUBUa5A0R6JyINcjNesRadPOR7HY+iQNrekkKVMlOgSH/0ulL063oUrbuM08bfMwTLpH+2wsQyn2greX4eMaZiAeJ9CAOR7/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IncidentId = _t, AccountName = _t, AccountNumber = _t, AccountType = _t, CallDate = _t, CallTime = _t, MajorComment = _t, MinorComment = _t, ThirdTier = _t, AccountDuration = _t, TotalCalls = _t, CallNumber = _t, TotalDuration = _t, SeqDuration = _t, FirstIncidentId = _t]),
#"Removed Columns" = Table.RemoveColumns(Source,{"AccountName", "AccountType", "MajorComment", "MinorComment", "ThirdTier", "CallTime", "TotalCalls", "CallNumber", "TotalDuration", "SeqDuration", "FirstIncidentId"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"CallDate", type date}}, "de-DE"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"AccountDuration", Int64.Type}}),
fnSummarizeIncidents = (tbl as table) as table =>
let
SortByCallDate = Table.Sort(tbl, {{"CallDate", Order.Ascending}}),
IndexColumn = Table.AddIndexColumn(SortByCallDate, "Index", 0, 1),
// a new serie starts if the count of days between 2 incidents of one account is longer than days
IncidentSerieStart = Table.AddColumn(IndexColumn, "IncidentSerieStart", each
if [Index] = 0 then
1
else if Duration.Days([CallDate] - tbl[CallDate]{[Index]-1}) >= 7 then
1
else
0
),
// sum all previous IncidentSerieStarts to have an ID for a serie
IncidentSerieNumber = Table.AddColumn(IncidentSerieStart, "IncidentSerieNumber",
(parentRecord) =>
List.Sum(
Table.Column(
Table.SelectRows(
IncidentSerieStart,
(childRecord) => parentRecord[Index] >= childRecord[Index]
),
"IncidentSerieStart"
)
)
),
// group by series
Groups = Table.Group(IncidentSerieNumber, {"IncidentSerieNumber"}, {{"IncidentSerie", each _}}),
// remove incident serie number
RemoveIncidentSerieNumber = Table.RemoveColumns(Groups,{"IncidentSerieNumber"}),
// add an inner index for every group
GroupInnerIndex = Table.TransformColumns(RemoveIncidentSerieNumber, {"IncidentSerie", each Table.AddIndexColumn(_, "SerieIndex", 1, 1)}),
// calculate a running total of AccountDuration
GroupInnerRunningTotalOfAccountDuration = Table.TransformColumns(GroupInnerIndex, {"IncidentSerie", (tbl) => Table.AddColumn(tbl, "Sequence Duration", (rec) =>
List.Sum(Table.Column(Table.SelectRows(tbl, each [SerieIndex] <= rec[SerieIndex]), "AccountDuration"))
)}),
// aggregations
TotalCalls = Table.AddColumn(GroupInnerRunningTotalOfAccountDuration, "Total Calls", each Table.RowCount([IncidentSerie]), Int64.Type),
TotalDuration = Table.AddColumn(TotalCalls, "Total Duration", each List.Sum(Table.Column([IncidentSerie], "AccountDuration"))),
FirstIncident = Table.AddColumn(TotalDuration, "First Incident", each List.Min(Table.Column([IncidentSerie], "IncidentId"))),
// expand group
ExpandTable = Table.ExpandTableColumn(
FirstIncident,
"IncidentSerie",
{"IncidentId", "AccountNumber", "CallDate", "AccountDuration", "SerieIndex", "Sequence Duration"}
)
in
ExpandTable,
Groups = Table.Group(#"Changed Type", {"AccountNumber"}, {{"PerAccount", each _}}),
RemoveAccountNumber = Table.RemoveColumns(Groups, "AccountNumber"),
GroupsSummarized = Table.TransformColumns(RemoveAccountNumber, {"PerAccount", fnSummarizeIncidents}),
ExpandTable = Table.ExpandTableColumn(GroupsSummarized, "PerAccount", {"IncidentId", "AccountNumber", "CallDate", "AccountDuration", "SerieIndex", "Sequence Duration", "Total Calls", "Total Duration", "First Incident"})
in
ExpandTable
