Hey Team!
I have very complex problem I'm struggling to resolve. I assume there is several ways to do it. I'm looking for performance wise aproach that can handle milions of rows.
Goal: summarize real edit time (from Agent perspective)
Problem: When editing a Case system create duplicate record if agent goes from Case to WO. New row/record is created for same Case.
Duplicate: duplicate record is row with Work Order ID. At least part of the time edit is simultaneous with Case edit. When Agent end edit mode (End Time) then Case Edit is automatically ended usually 7-9 seconds later. The Work Order edit is thus duplicate and for tracking purposes we need only Case row
Solution: I tried chatgpt proposal adding new custom column that returns end time of previous Case edit (based on matching Case Number and Start by which is same agent. After I sorted data and used Buffer.Table to keep correct sorting.
#"Added End Time From Case Row" = Table.AddColumn(#"Added Index", "End Time (From Case)", each
if [Work Order] <> null then
let
CurrentCase = [#"Case Number (Case) (Case)"],
CurrentStartBy = [Start By],
// Find the first row where "Work Order" is null and Case Number and Start By match
MatchedRow = Table.SelectRows(#"Added Index", each
[#"Case Number (Case) (Case)"] = CurrentCase and
[Start By] = CurrentStartBy and
[Work Order] = null
){0}?, // Use "{0}?" to safely return the first matching row or null if no match
EndTimeValue = if MatchedRow <> null then MatchedRow[End Time] else null
in
EndTimeValue
else
null
Do you think there is better solution to this problem that is more performance friendly or in general better approach that consider row calculation based on same condition ? Thank you for your help!
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
