Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago
    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".

lbendlin's avatar
lbendlin
Super User
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

https://docs.google.com/spreadsheets/d/1AS8N6t3KfAosk1D1tQwyPAKWBkbPKPMS/edit?usp=sharing&ouid=110340216374104176999&rtpof=true&sd=true

Ticket Id   Entered Date               New Status                                       Old Status

13362929/24/2021 4:42:21 PMClosed - No Action NecessaryResolved - No Action Necessary
13362929/15/2021 9:35:33 PMResolved - No Action NecessaryService - New
134853711/5/2021 8:59:35 PMResolved - Completed NexigenService - Assigned
13403179/30/2021 9:25:18 PMClosed - No Action NecessaryNew
13387269/25/2021 2:09:43 PMClosed - No Action NecessaryResolved - No Action Necessary
13387269/25/2021 1:56:12 PMResolved - No Action NecessaryService - New
13387269/25/2021 12:56:15 PMService - NewService - New
13292648/18/2021 8:05:09 PMOn Hold - Future EventService - In Progress
134205510/15/2021 8:33:34 PMClosed - CompletedResolved - Completed
13282429/14/2021 2:42:48 PMService - Scheduled - RemoteService - In Progress
110603112/9/2018 1:16:26 AMResolvedNew
127201112/15/2020 3:41:07 PMClosed - CompletedResolved - By Mike's IT
11114052/27/2019 6:07:22 PMClosed - ResolvedResolved
134205510/7/2021 2:28:30 PMResolved - CompletedService - In Progress
13392419/28/2021 12:14:14 PMService - Needs Reviewed by NexigenService - New
13926005/4/2022 7:25:06 PMService - In ProgressService - Scheduled - Remote
134080010/1/2021 5:08:10 PMService - NewService - New
11114051/3/2019 8:26:11 PMResolvedEscalated To Client IT
13774393/21/2022 6:14:46 PMClosed - CompletedResolved - Completed
13774393/11/2022 12:54:46 PMResolved - CompletedService - In Progress
13946635/9/2022 10:30:12 PMResolved - BundledService - New
13985445/26/2022 9:11:50 PMClosed - CompletedResolved - Completed
13941485/9/2022 6:34:21 AMClosed - OtherResolved - No Action Necessary
13941485/8/2022 6:04:14 AMResolved - No Action NecessaryService - New
13941965/9/2022 7:47:02 AMCanceledResolved - Duplicate
13941965/8/2022 7:47:13 AMResolved - DuplicateService - New
14095216/30/2022 10:21:42 PMClosed - CompletedResolved - Completed
13944435/9/2022 1:47:02 AMClosed - No Action NecessaryService - New
13970135/25/2022 6:48:24 PMService - In ProgressOn Hold - Attempted Contact - 1st
13970135/25/2022 6:41:32 PMOn Hold - Attempted Contact - 1stService - Scheduled - Remote
13970595/18/2022 8:31:57 PMService - Scheduled - RemoteEscalate - Needs Scheduled
13992275/31/2022 11:01:11 AMClosed - CompletedResolved - Completed Nexigen
13993995/26/2022 6:39:07 PMService - AssignedService - Assign to Service Desk
13958675/20/2022 9:32:56 PMClosed - CompletedResolved - Completed
13827833/28/2022 12:09:03 PMOn Hold - Attempted Contact - 2ndService - Needs Reviewed by Nexigen
13830943/27/2022 4:35:56 AMService - NewService - New
13849744/5/2022 8:58:11 PMResolved - Awaiting VerificationService - In Progress
13775963/17/2022 4:21:26 PMResolved - CompletedService - Scheduled - Remote
11998382/3/2020 9:58:09 PMClosed - ResolvedResolved
11998381/24/2020 3:40:12 PMResolvedOn Hold - Awaiting Client Approval
12838063/31/2022 4:11:43 PMClosed - Do Not NotifyEscalate - Escalated To Auto Valve
10072453/17/2018 10:35:09 AMClosed - ResolvedNo Action Necessary
10072453/17/2018 9:43:06 AMNo Action NecessaryNew
12838062/7/2021 10:27:50 PMEscalate - Escalated To VinylMax ITService - New
13808553/28/2022 5:01:45 PMClosed - AutofixResolved - Bundled
13808553/17/2022 9:39:47 PMResolved - BundledService - New
13808553/17/2022 5:27:01 PMService - NewService - New
13806903/20/2022 1:49:46 PMResolved - Awaiting VerificationEscalate - Escalated to Michelman JDE
13840933/31/2022 1:43:01 PMService - AssignedService - Assign to Service Desk
13832743/31/2022 7:27:50 PMClosed - By Michelman ITResolved - Completed - Michelman