Python extension running really slow

[Anonymous]

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 by my last few lines of code, that actually edit my data. The column is created without any issue, but when I try to update the column with the new information, the scrip runs forever as far as I can tell (I have seen it run for upwards of a half hour without finishing).

Initially I thought it may be because of the volume of my data, so I tried testing it with only the first 50 rows of my data, and it still never completed running the script. Here is my code:

import pandas as pd

dataset["Duration In Status"] = "null"

for x in range(40):
    ticketID = dataset["Ticket ID"][x]
    initialStatus = dataset["Initial Status"]
    ticketable = pd.DataFrame({"Time of Change":dataset["Date"][x],
                               "Initial Status":dataset["Initial Status"][x],
                               "New Status":dataset["New Status"][x],
                               "Last Change": None}, index=[0])
    for y in range(40):
        if dataset["Ticket ID"][y] == dataset["Ticket ID"][x] and dataset["Date"][y] != dataset["Date"][x]:
            ticketable.loc[len(ticketable.index)] = [dataset["Date"][y],dataset["Initial Status"][y],dataset["New Status"][y],y]


    if dataset["Initial Status"][x] == ticketable["New Status"][0]:
        dataset["Duration In Status"][x] = ticketable["Time of Change"][z]
    else:
        dataset["Duration In Status"][x] = "New Ticket"

so far as I can tell, it is caused by the final if/else statement, and the changing of data that occurs within said statement. If I delete the if/else statement, the code completes in about 5 seconds with no issue, and the new column added to the table. Any help with a solution would be greatly appreciated.

[Anonymous]

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.

[lbendlin]

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]

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=11034...

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

[lbendlin · Accepted solution]

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".

[Anonymous]

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?

[lbendlin]

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.

[lbendlin]

For each of your data rows (how many do you have)  you are running 1600 iterations. Is that really necessary?

Maybe this can all be done in Power Query directly. Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.