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".
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?
Keep in mind that Power Query runs on disk. (But it should still be able to handle way more that 50 rows - are you doing any additional expensive transforms?)
You can consider implementing this logic in DAX instead. DAX runs in memory and is quite bit faster than Power Query.