Forum Discussion
Python extension running really slow
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVfbTuNIEP2VVp4Zpa/uy5uHsFpWAkYD4oXlwZs0jIWxo9gJ8Pdbbbvt+EZCZqQgktg5XVXn1Knyw8PsLl6+2AJdLmZns4u0sBu7QouosPDx2r6h2yIqtjl8uElW/sPj2cOMMBZQTeGCnlM+p5gSxA2nBv7/uIKvz5MsB6hv6DpD4bKIsxRd26XN82jzAZd/2jxLdpM39I8gojpCGyYMY9URBzDOZrd2s4uX1l23bzUmV4JJuEbIvMZURjjYAeZ59rpObAHvr+17/GzTDmKY5/FzalceFjMiy1AZ9qFSYYg6qhpteExJGlRVrcOjBmvD2R+s6uAIYkRgCP2Nqo5g0hK0Lmv3N+MYwHXA4ZqaE+WZwQKyryBuUvR3lriw/toW241FFzubFh2syxT92GTPGwjV00KxEI5t3EhIgX4M4716NmRPaMDHqCivFck9PSB6rvpp3i5/2dU2KUF+2tesbKhPIiU4AAW5SOkciohBOMSQwNAAhfus7IuFSriv/k2VHUbMcGKwPC677x/oKn6x/24xpjJHl3d1LIRw7KpG51S6WDQKANNQ2oPdC6p5O6i79HWiUHk83WUHmGSaclIpTDUKIxxeQ4XZVQ6x7WL7Bkf89zHav63uQHYYwzUxLymlSLrOxUEfdz+mA1R7T1AlsBNfFTLAKkPw0T3RMEHmrCJCgSIMId0yOuvOl1ESObO6y9B5EkNrNHwyKTnTcBNgkCrFwJWOB6c1QQtHajjX7C3eCexqHgSs5EDXiBjEMuZJ37fpKunh7XGpBOclDg0qIA3VMgKflqnmhKv9sAJwDjfiwg7aTfHLbo613xZTeUxcyjg82X0BUgf7YUrDpcG0DjNKlzbpp7rYrpN4GTVibTHUHgZhg7DaH44Gw7EW1DVqUE/CkktKwCZP5YDzrjQ6yX0+EEerJTGpAKtZ5Rjg0FcDI+k2fDt/wqKwr2vXa+dZWkTLAr4jefEZPDGM9sfYFMxR3gKnCF2eQmq+wF1B6PKoQeTdonHL5i4PrymVJTzzLQ5jhTjnCb9EYuO9Hhdenf6EjtLNwBpZr4ZfoiJD/ruFzV88tFBBFTLFvvWZW0JOk52iUrHKM1VjcrCJYXYMjzTtO9TUTKpPY1jz6jRZncbdpivq6X/M+qS4lg6Cz4UXhFCDUeHifYviIk6f0b3dxE+ulaFrDvizlKJ0B7D8Jj7oaXqs40/pmGitmCpXDVYtMNpF7Xe+w5tGA0Dqx5ByBRpOji5jvgL1oAzX6022i5J6rQJAXCXrxc/dEBns4IsMXKdwf/HTR7erOuM43IJi7yMIozoAY0m5aKvpdj3s6Ia0w6m0JwfKCJp7XnALTAl24ImjyZb6Tc3ZtWxG5lRS93H6kVxF727LmBAkVkJ0Okg4C+GiV0ZXnqf4fXzM96G8/LSzDS6/uB4McYTLFZPjn1KgWBpXWeFmHumx3Weqz0YrCgq5iqFJktcoRf8sLpqmxpp1pEhKbgcBf90vFaOSd6DlPu8NPeVDgo+sJHvU57+1d80eH/8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Entered Date", type datetime}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Ticket ID", Order.Ascending}, {"Entered Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Duration", each if [Index]=0 then null else if [Ticket ID]=#"Sorted Rows"[Ticket ID]{[Index]-1} then [Entered Date]-#"Sorted Rows"[Entered Date]{[Index]-1} else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Here is a sample dataset, The goal is to get the duration time between status changes for tickets. For example, in for row 22, the initial status is Resolved- do not notify, and the new status is closed - do not notify. The script would look for a row with with the same ticket number, and a New status that is the same as column 22s old status. This would be row 21, which has the resolved - do not notify new status. I am planning to then switch out to power Query to find the duration between the two dates, and return that as my final value, which in this case, would be 1 hour and 2 minutes.
If this can all be done through power query directly, please let me know! I tried for a while to figure out how to do it but couldn't figure it out, so I tried using the python integration as I know much more about python.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Here is a table with the information, I deleted the audit text column, as it has private information, but it does not have any bearing on the python formula. If this doesn't work I also have a link to an excel sheet with the data in my google drive
Ticket Id Entered Date New Status Old Status
1336292 9/24/2021 4:42:21 PM Closed - No Action Necessary Resolved - No Action Necessary 1336292 9/15/2021 9:35:33 PM Resolved - No Action Necessary Service - New 1348537 11/5/2021 8:59:35 PM Resolved - Completed Nexigen Service - Assigned 1340317 9/30/2021 9:25:18 PM Closed - No Action Necessary New 1338726 9/25/2021 2:09:43 PM Closed - No Action Necessary Resolved - No Action Necessary 1338726 9/25/2021 1:56:12 PM Resolved - No Action Necessary Service - New 1338726 9/25/2021 12:56:15 PM Service - New Service - New 1329264 8/18/2021 8:05:09 PM On Hold - Future Event Service - In Progress 1342055 10/15/2021 8:33:34 PM Closed - Completed Resolved - Completed 1328242 9/14/2021 2:42:48 PM Service - Scheduled - Remote Service - In Progress 1106031 12/9/2018 1:16:26 AM Resolved New 1272011 12/15/2020 3:41:07 PM Closed - Completed Resolved - By Mike's IT 1111405 2/27/2019 6:07:22 PM Closed - Resolved Resolved 1342055 10/7/2021 2:28:30 PM Resolved - Completed Service - In Progress 1339241 9/28/2021 12:14:14 PM Service - Needs Reviewed by Nexigen Service - New 1392600 5/4/2022 7:25:06 PM Service - In Progress Service - Scheduled - Remote 1340800 10/1/2021 5:08:10 PM Service - New Service - New 1111405 1/3/2019 8:26:11 PM Resolved Escalated To Client IT 1377439 3/21/2022 6:14:46 PM Closed - Completed Resolved - Completed 1377439 3/11/2022 12:54:46 PM Resolved - Completed Service - In Progress 1394663 5/9/2022 10:30:12 PM Resolved - Bundled Service - New 1398544 5/26/2022 9:11:50 PM Closed - Completed Resolved - Completed 1394148 5/9/2022 6:34:21 AM Closed - Other Resolved - No Action Necessary 1394148 5/8/2022 6:04:14 AM Resolved - No Action Necessary Service - New 1394196 5/9/2022 7:47:02 AM Canceled Resolved - Duplicate 1394196 5/8/2022 7:47:13 AM Resolved - Duplicate Service - New 1409521 6/30/2022 10:21:42 PM Closed - Completed Resolved - Completed 1394443 5/9/2022 1:47:02 AM Closed - No Action Necessary Service - New 1397013 5/25/2022 6:48:24 PM Service - In Progress On Hold - Attempted Contact - 1st 1397013 5/25/2022 6:41:32 PM On Hold - Attempted Contact - 1st Service - Scheduled - Remote 1397059 5/18/2022 8:31:57 PM Service - Scheduled - Remote Escalate - Needs Scheduled 1399227 5/31/2022 11:01:11 AM Closed - Completed Resolved - Completed Nexigen 1399399 5/26/2022 6:39:07 PM Service - Assigned Service - Assign to Service Desk 1395867 5/20/2022 9:32:56 PM Closed - Completed Resolved - Completed 1382783 3/28/2022 12:09:03 PM On Hold - Attempted Contact - 2nd Service - Needs Reviewed by Nexigen 1383094 3/27/2022 4:35:56 AM Service - New Service - New 1384974 4/5/2022 8:58:11 PM Resolved - Awaiting Verification Service - In Progress 1377596 3/17/2022 4:21:26 PM Resolved - Completed Service - Scheduled - Remote 1199838 2/3/2020 9:58:09 PM Closed - Resolved Resolved 1199838 1/24/2020 3:40:12 PM Resolved On Hold - Awaiting Client Approval 1283806 3/31/2022 4:11:43 PM Closed - Do Not Notify Escalate - Escalated To Auto Valve 1007245 3/17/2018 10:35:09 AM Closed - Resolved No Action Necessary 1007245 3/17/2018 9:43:06 AM No Action Necessary New 1283806 2/7/2021 10:27:50 PM Escalate - Escalated To VinylMax IT Service - New 1380855 3/28/2022 5:01:45 PM Closed - Autofix Resolved - Bundled 1380855 3/17/2022 9:39:47 PM Resolved - Bundled Service - New 1380855 3/17/2022 5:27:01 PM Service - New Service - New 1380690 3/20/2022 1:49:46 PM Resolved - Awaiting Verification Escalate - Escalated to Michelman JDE 1384093 3/31/2022 1:43:01 PM Service - Assigned Service - Assign to Service Desk 1383274 3/31/2022 7:27:50 PM Closed - By Michelman IT Resolved - Completed - Michelman let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVfbTuNIEP2VVp4Zpa/uy5uHsFpWAkYD4oXlwZs0jIWxo9gJ8Pdbbbvt+EZCZqQgktg5XVXn1Knyw8PsLl6+2AJdLmZns4u0sBu7QouosPDx2r6h2yIqtjl8uElW/sPj2cOMMBZQTeGCnlM+p5gSxA2nBv7/uIKvz5MsB6hv6DpD4bKIsxRd26XN82jzAZd/2jxLdpM39I8gojpCGyYMY9URBzDOZrd2s4uX1l23bzUmV4JJuEbIvMZURjjYAeZ59rpObAHvr+17/GzTDmKY5/FzalceFjMiy1AZ9qFSYYg6qhpteExJGlRVrcOjBmvD2R+s6uAIYkRgCP2Nqo5g0hK0Lmv3N+MYwHXA4ZqaE+WZwQKyryBuUvR3lriw/toW241FFzubFh2syxT92GTPGwjV00KxEI5t3EhIgX4M4716NmRPaMDHqCivFck9PSB6rvpp3i5/2dU2KUF+2tesbKhPIiU4AAW5SOkciohBOMSQwNAAhfus7IuFSriv/k2VHUbMcGKwPC677x/oKn6x/24xpjJHl3d1LIRw7KpG51S6WDQKANNQ2oPdC6p5O6i79HWiUHk83WUHmGSaclIpTDUKIxxeQ4XZVQ6x7WL7Bkf89zHav63uQHYYwzUxLymlSLrOxUEfdz+mA1R7T1AlsBNfFTLAKkPw0T3RMEHmrCJCgSIMId0yOuvOl1ESObO6y9B5EkNrNHwyKTnTcBNgkCrFwJWOB6c1QQtHajjX7C3eCexqHgSs5EDXiBjEMuZJ37fpKunh7XGpBOclDg0qIA3VMgKflqnmhKv9sAJwDjfiwg7aTfHLbo613xZTeUxcyjg82X0BUgf7YUrDpcG0DjNKlzbpp7rYrpN4GTVibTHUHgZhg7DaH44Gw7EW1DVqUE/CkktKwCZP5YDzrjQ6yX0+EEerJTGpAKtZ5Rjg0FcDI+k2fDt/wqKwr2vXa+dZWkTLAr4jefEZPDGM9sfYFMxR3gKnCF2eQmq+wF1B6PKoQeTdonHL5i4PrymVJTzzLQ5jhTjnCb9EYuO9Hhdenf6EjtLNwBpZr4ZfoiJD/ruFzV88tFBBFTLFvvWZW0JOk52iUrHKM1VjcrCJYXYMjzTtO9TUTKpPY1jz6jRZncbdpivq6X/M+qS4lg6Cz4UXhFCDUeHifYviIk6f0b3dxE+ulaFrDvizlKJ0B7D8Jj7oaXqs40/pmGitmCpXDVYtMNpF7Xe+w5tGA0Dqx5ByBRpOji5jvgL1oAzX6022i5J6rQJAXCXrxc/dEBns4IsMXKdwf/HTR7erOuM43IJi7yMIozoAY0m5aKvpdj3s6Ia0w6m0JwfKCJp7XnALTAl24ImjyZb6Tc3ZtWxG5lRS93H6kVxF727LmBAkVkJ0Okg4C+GiV0ZXnqf4fXzM96G8/LSzDS6/uB4McYTLFZPjn1KgWBpXWeFmHumx3Weqz0YrCgq5iqFJktcoRf8sLpqmxpp1pEhKbgcBf90vFaOSd6DlPu8NPeVDgo+sJHvU57+1d80eH/8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Entered Date", type datetime}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Ticket ID", Order.Ascending}, {"Entered Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Duration", each if [Index]=0 then null else if [Ticket ID]=#"Sorted Rows"[Ticket ID]{[Index]-1} then [Entered Date]-#"Sorted Rows"[Entered Date]{[Index]-1} else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Sorry for the belated reply, I really appreciate your help so far. I've tried modifying your code a little as the actual data is imported from an SQL database, and I am still having the issue of powerquery running the code very slowly, even after trimming it down to 50 rows. I've confirmed it does end up running successfully at least, which is a step foreward, and does output what I am looking for. I am just worried that as more info is added to the table, it will take longer and longer to work. Any advice as to helping speed it up?