Complicated Phone System Log Question

[Thomas_MedOne]

So, this is a complicated one and I hope you can help.  We have a Zoom phone system and the logs that are produced are hierarchical, kinda, It has columns but not all columns have data. like it lists call number 1 and then has rows for each step along the way. 

I brought it into Power Query and got the rows to be properly associated with one another and then created an 'ordinal' which keeps them in the right order.  So, now the (relevant) data looks order of like this:

I need a way to kinda flatten this data to one row for each call showing the information I need for the call. Like, this is one call here but I need specific information out of it for instance:

• Start & End Time: would be the "Start Time" and "DurationCalc" field in row one.
• Wait Time: Would be "Wait Time Calc" from the "Forward" event line.
• The "Ring to Member" Time would be "Start Time" field for the "Ring to Member" event time
• The Total Talk time would be the "DurationTime" value from the "Ring to Member" line that ended up talking

Ultimately, I would also want to know the gap between when the last call hung up and the next call rang to a member. But that has conditions.  So, if the incoming call happens while someone in already on the phone, I'm comparing how long it took after the call was hung up until their phone rang again.  But if the incoming call is not during an active call then I want to know how long from when the call started to when the person's phone rang.

The reason for the last calculation is we're fighting with Zoom about how sometimes calls are "stuck" in the queue and not ringing the person even though they're available.

I don't know if any of this stuff is possible and perhaps I just need to contact an expert consultant and pay them to get this flattened out. I don't know.  Any help or information you all could provide would be helpful.

[v-veshwara-msft · Accepted solution]

Hi @Thomas_MedOne ,

Thanks for posting in Microsoft Fabric Community and for the detailed explanation.

Flattening this type of event log structure into a single row per call is achievable in Power Query by following a systematic approach:

1. Group the data by the Call-ID-Ord to bring all related rows under each unique call. This ensures all event types (like "Incoming," "Forward," "Ring to Member") are kept together.

2. Use custom columns with conditional logic (for example, using Table.SelectRows or Table.AddColumn with if conditions) to extract values from specific event rows:

         Start Time and DurationCalc from the "Incoming" row.
         WaitTimeCalc from the "Forward" row.
         Start Time and DurationTime from the "Ring to Member" row (the final one where the call is answered).

3. For the gap between calls logic, you can use sorting by Start Time and indexing the rows, then calculating the difference between the end of one call and the ring time (or start time) of the next call. This may involve additional logic to check if the calls overlap (based on call durations) or are independent.

4. You may also consider creating conditional flags (for example, "Call Overlaps" or "Available but not ringing") to track if the agent was available but did not receive a call during that gap period.

Here are some similar resources:

Flattening parent-child hierarchy in Power Query

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[Akash_Varuna]

Hi @Thomas_MedOne For this, you can group the data by Call-ID-Ord to handle each call as a single entity. Then, filter rows within each group to extract Start Time from "Incoming", Wait Time from "Forward", Answered Time from "Ring to Member", and Total Talk Time from the "Ring to Member" row. Flatten the grouped data so each call is represented in a single row with all the required metrics. Finally, calculate the gap between calls if needed for further analysis.