Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to calculate the number of times a customer has called our support line after the initial call.
Example
Example Company calls for support on 04/12/2018, and does not call again within 7 days, this is 1 call.
Example Company calls for support again on 03/05/2019 a total of 6 times and also on the 04/05/2019 2 times, 05/05/2019 1 time, and 11/05/2019 1 time. This should count as 10 calls because although the first call took place on the 03/05/2019 and 7 days after would be the 10/05/2019 the period should be dynamic to include the 11/05/2019 as that was within 7 days of 05/05/2019 which was within 7 days of 03/05/2019
Example Company calls for support again on 10/06/2019, and does not call again within 7 days, this is 1 call.
Current Data
IncidentId | AccountName | AccountNumber | AccountType | CallDate | CallTime | MajorComment | MinorComment | ThirdTier | AccountDuration |
33059568 | Example Company | 98765432 | Customer | 04/12/2018 | 16:20:26 | Payroll | RTI | FPS | 728 |
33925147 | Example Company | 98765432 | Customer | 03/05/2019 | 09:50:39 | Payroll | Installation and Activation | Install Program | 735 |
33928425 | Example Company | 98765432 | Customer | 03/05/2019 | 14:13:58 | Payroll | Installation and Activation | Install Program | 828 |
33928851 | Example Company | 98765432 | Customer | 03/05/2019 | 14:46:08 | Payroll | Installation and Activation | Install Program | 429 |
33929819 | Example Company | 98765432 | Customer | 03/05/2019 | 16:13:20 | Payroll | Installation and Activation | Install Program | 1255 |
33930022 | Example Company | 98765432 | Customer | 03/05/2019 | 16:29:46 | Payroll | Installation and Activation | Install Program | 561 |
33930533 | Example Company | 98765432 | Customer | 03/05/2019 | 17:40:37 | Payroll | Installation and Activation | Install Program | 1952 |
33935776 | Example Company | 98765432 | Customer | 04/05/2019 | 15:52:54 | Payroll | Setup and Manage | Employee | 206 |
33936491 | Example Company | 98765432 | Customer | 04/05/2019 | 16:58:43 | Payroll | Processing | Correcting a mistake | 3359 |
33942334 | Example Company | 98765432 | Customer | 05/05/2019 | 16:34:21 | Payroll | Processing | Correcting a mistake | 3767 |
33951852 | Example Company | 98765432 | Customer | 11/05/2019 | 13:32:33 | Payroll | Processing | Calculations | 266 |
33935776 | Example Company | 98765432 | Customer | 10/06/2019 | 15:52:54 | Payroll | Setup and Manage | Employee | 206 |
What I want to achieve in bold
IncidentId | AccountName | AccountNumber | AccountType | CallDate | CallTime | MajorComment | MinorComment | ThirdTier | AccountDuration | TotalCalls | CallNumber | TotalDuration | SeqDuration | FirstIncidentId |
33059568 | Example Company | 98765432 | Customer | 04/12/2018 | 16:20:26 | Payroll | RTI | FPS | 728 | 1 | 1 | 728 | 728 | 33059568 |
33925147 | Example Company | 98765432 | Customer | 03/05/2019 | 09:50:39 | Payroll | Installation and Activation | Install Program | 735 | 10 | 1 | 13358 | 735 | 33925147 |
33928425 | Example Company | 98765432 | Customer | 03/05/2019 | 14:13:58 | Payroll | Installation and Activation | Install Program | 828 | 10 | 2 | 13358 | 1563 | 33925147 |
33928851 | Example Company | 98765432 | Customer | 03/05/2019 | 14:46:08 | Payroll | Installation and Activation | Install Program | 429 | 10 | 3 | 13358 | 1992 | 33925147 |
33929819 | Example Company | 98765432 | Customer | 03/05/2019 | 16:13:20 | Payroll | Installation and Activation | Install Program | 1255 | 10 | 4 | 13358 | 3247 | 33925147 |
33930022 | Example Company | 98765432 | Customer | 03/05/2019 | 16:29:46 | Payroll | Installation and Activation | Install Program | 561 | 10 | 5 | 13358 | 3808 | 33925147 |
33930533 | Example Company | 98765432 | Customer | 03/05/2019 | 17:40:37 | Payroll | Installation and Activation | Install Program | 1952 | 10 | 6 | 13358 | 5760 | 33925147 |
33935776 | Example Company | 98765432 | Customer | 04/05/2019 | 15:52:54 | Payroll | Setup and Manage | Employee | 206 | 10 | 7 | 13358 | 5966 | 33925147 |
33936491 | Example Company | 98765432 | Customer | 04/05/2019 | 16:58:43 | Payroll | Processing | Correcting a mistake | 3359 | 10 | 8 | 13358 | 9325 | 33925147 |
33942334 | Example Company | 98765432 | Customer | 05/05/2019 | 16:34:21 | Payroll | Processing | Correcting a mistake | 3767 | 10 | 9 | 13358 | 13092 | 33925147 |
33951852 | Example Company | 98765432 | Customer | 11/05/2019 | 13:32:33 | Payroll | Processing | Calculations | 266 | 10 | 10 | 13358 | 13358 | 33925147 |
33935776 | Example Company | 98765432 | Customer | 10/06/2019 | 15:52:54 | Payroll | Setup and Manage | Employee | 206 | 1 | 1 | 206 | 207 | 33935776 |
Solved! Go to Solution.
Hi @mark_carlisle,
here is a solution for you. It seaches recursively for all incident within of plus minus 7 days for an accountId and then aggregates the data for you.
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}}), fnGetAllCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date, curIncidentIds as list) as table => let // filter out all rows having max distance of 7 days of the same AccountNumber FilteredTable = Table.SelectRows( tbl, each [AccountNumber] = accountNumber and Number.Abs(Duration.Days([CallDate] - curDate)) <= 7 and not List.Contains(curIncidentIds, [IncidentId]) ), Result = if Table.IsEmpty(FilteredTable) then FilteredTable // return from recursion else let // a list of all visited incidents newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}), // recursive all incidents before minimum CallDate of already found incidents checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDays( tbl, accountNumber, Table.Min(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // recursive all incidents after maximum CallDate of already found incidents checkAfterCurrentDate = @fnGetAllCallsWithinSevenDays( tbl, accountNumber, Table.Max(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // combine all tables coming from recursion combinedResult = Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate}) in combinedResult in Result, // unique records fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table => let distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDays(tbl, accountNumber, curDate, {}), "IncidentId"), indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1) in indexedTable, // unique records within seven days runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])), // aggregations TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type), CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]), TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))), SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])), FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId"))) in FirstIncident
Hi @mark_carlisle,
I have some optimization ideas, please test it with your big table or publish a csv file with needed columns {"IncidentId", "AccountDuration", "CallDate", "AccountNumber"}. They don't contain any sensitive data.
What is improved:
Another ideas:
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}}), fnGetAllCallsWithinSevenDaysOfOneAccount = (tbl as table, curDate as date, curIncidentIds as list) as table => let // filter out all rows having max distance of 7 days of the same AccountNumber FilteredTable = Table.SelectRows( tbl, each Number.Abs(Duration.Days([CallDate] - curDate)) <= 7 and not List.Contains(curIncidentIds, [IncidentId]) ), Result = if Table.IsEmpty(FilteredTable) then FilteredTable // return from recursion else let // a list of all visited incidents newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}), // recursive all incidents before minimum CallDate of already found incidents checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDaysOfOneAccount( tbl, Table.Min(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // recursive all incidents after maximum CallDate of already found incidents checkAfterCurrentDate = @fnGetAllCallsWithinSevenDaysOfOneAccount( tbl, Table.Max(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // combine all tables coming from recursion combinedResult = Table.Distinct(Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate})) in combinedResult in Result, // unique records fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table => let filteredRowsByAccountNumber = Table.SelectRows(tbl, each [AccountNumber] = accountNumber), filteredColumns = Table.SelectColumns(filteredRowsByAccountNumber, {"IncidentId", "AccountDuration", "CallDate"}), // NOTE: if you knew that no sequence of incidents is longer than i.e. +-2 month, you can also dramaticaly reduce the searched space and therefore increase performance filteredRowsByDate = filteredColumns, // Table.SelectRows(filteredColumns, each [CallDate] > Date.AddMonths(curDate, -2) and [CallDate] < Date.AddMonths(curDate, 2)), distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDaysOfOneAccount(filteredRowsByDate, curDate, {}), "IncidentId"), indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1) in indexedTable, // unique records within seven days runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])), // aggregations TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type), CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]), TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))), SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])), FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId"))) in FirstIncident
Hi @mark_carlisle,
here is a solution for you. It seaches recursively for all incident within of plus minus 7 days for an accountId and then aggregates the data for you.
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}}), fnGetAllCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date, curIncidentIds as list) as table => let // filter out all rows having max distance of 7 days of the same AccountNumber FilteredTable = Table.SelectRows( tbl, each [AccountNumber] = accountNumber and Number.Abs(Duration.Days([CallDate] - curDate)) <= 7 and not List.Contains(curIncidentIds, [IncidentId]) ), Result = if Table.IsEmpty(FilteredTable) then FilteredTable // return from recursion else let // a list of all visited incidents newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}), // recursive all incidents before minimum CallDate of already found incidents checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDays( tbl, accountNumber, Table.Min(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // recursive all incidents after maximum CallDate of already found incidents checkAfterCurrentDate = @fnGetAllCallsWithinSevenDays( tbl, accountNumber, Table.Max(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // combine all tables coming from recursion combinedResult = Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate}) in combinedResult in Result, // unique records fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table => let distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDays(tbl, accountNumber, curDate, {}), "IncidentId"), indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1) in indexedTable, // unique records within seven days runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])), // aggregations TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type), CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]), TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))), SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])), FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId"))) in FirstIncident
@Nolock I've been testing this with actual data around 130,000 rows (for a month), although ideally I'd like to bring in a years worth of data (~1.7 million rows) and the query never loads, or at least not in a reasonable time.
Are there any tips on improving performance on this or am I attempting too much with Power Query given the volume of data.
Hi @mark_carlisle,
I have some optimization ideas, please test it with your big table or publish a csv file with needed columns {"IncidentId", "AccountDuration", "CallDate", "AccountNumber"}. They don't contain any sensitive data.
What is improved:
Another ideas:
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}}), fnGetAllCallsWithinSevenDaysOfOneAccount = (tbl as table, curDate as date, curIncidentIds as list) as table => let // filter out all rows having max distance of 7 days of the same AccountNumber FilteredTable = Table.SelectRows( tbl, each Number.Abs(Duration.Days([CallDate] - curDate)) <= 7 and not List.Contains(curIncidentIds, [IncidentId]) ), Result = if Table.IsEmpty(FilteredTable) then FilteredTable // return from recursion else let // a list of all visited incidents newIncidentIds = List.Combine({curIncidentIds, Table.Column(FilteredTable, "IncidentId")}), // recursive all incidents before minimum CallDate of already found incidents checkBeforeCurrentDate = @fnGetAllCallsWithinSevenDaysOfOneAccount( tbl, Table.Min(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // recursive all incidents after maximum CallDate of already found incidents checkAfterCurrentDate = @fnGetAllCallsWithinSevenDaysOfOneAccount( tbl, Table.Max(FilteredTable, "CallDate")[CallDate], newIncidentIds ), // combine all tables coming from recursion combinedResult = Table.Distinct(Table.Combine({checkBeforeCurrentDate, FilteredTable, checkAfterCurrentDate})) in combinedResult in Result, // unique records fnGetUniqueCallsWithinSevenDays = (tbl as table, accountNumber as any, curDate as date) as table => let filteredRowsByAccountNumber = Table.SelectRows(tbl, each [AccountNumber] = accountNumber), filteredColumns = Table.SelectColumns(filteredRowsByAccountNumber, {"IncidentId", "AccountDuration", "CallDate"}), // NOTE: if you knew that no sequence of incidents is longer than i.e. +-2 month, you can also dramaticaly reduce the searched space and therefore increase performance filteredRowsByDate = filteredColumns, // Table.SelectRows(filteredColumns, each [CallDate] > Date.AddMonths(curDate, -2) and [CallDate] < Date.AddMonths(curDate, 2)), distinctTable = Table.Distinct(fnGetAllCallsWithinSevenDaysOfOneAccount(filteredRowsByDate, curDate, {}), "IncidentId"), indexedTable = Table.AddIndexColumn(distinctTable, "Index", 1) in indexedTable, // unique records within seven days runningTotalsTable = Table.AddColumn(#"Changed Type", "runningTotalsTable", each fnGetUniqueCallsWithinSevenDays(#"Changed Type", [AccountNumber], [CallDate])), // aggregations TotalCalls = Table.AddColumn(runningTotalsTable, "Total Calls", each Table.RowCount([runningTotalsTable]), Int64.Type), CallNumber = Table.AddColumn(TotalCalls, "Call Number", (rootRecord) => Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] = childRecord[IncidentId]){0}[Index]), TotalDuration = Table.AddColumn(CallNumber, "Total Duration", each List.Sum(Table.Column([runningTotalsTable], "AccountDuration"))), SequenceDuration = Table.AddColumn(TotalDuration, "Sequence Duration", (rootRecord) => List.Sum(Table.SelectRows(rootRecord[runningTotalsTable], (childRecord) => rootRecord[IncidentId] >= childRecord[IncidentId])[AccountDuration])), FirstIncident = Table.AddColumn(SequenceDuration, "First Incident", each List.Min(Table.Column([runningTotalsTable], "IncidentId"))) in FirstIncident
That's magic. Thanks for the assistance.
Could I ask you for some pointers on resources for learning Power Query, specifically functions. I've used them for other things but nothing like this.
Hi @mark_carlisle,
I've read this blog as introduction: https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi... and the rest is just imagination and solving real-life problems at work and on this forum.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.