Forum Discussion
Python extension running really slow
Hi, I'm creating a script that adds a new column to my data, and it seems to be running very slowly. I've narrowed down what is causing it, but I do not know how I can fix it. It appears to be caused...
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".