Complicated Phone System Log Question

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.