title: Query to group these rows by date and user, starting a new group after specific event
body: <p>A call center has customers exceeding their line capacity, and has some customers hitting the busy signal as a result. Some customers will call over and over until they get through, and other customers will give up after a certain number of attempts.</p>
  
  <p>We have a CSV file exported from the phone switching software with records like this (heavily simplified) example:</p>
  
  <pre><code>TIME    NUMBER    RESULT
  10:00   8675309   Busy signal
  10:00   5551234   Busy signal
  10:01   8675309   Busy signal
  10:02   8675309   Answered
  11:00   5551234   Answered
  12:00   8675309   Busy signal
  12:01   8675309   Busy signal
  12:02   8675309   Busy signal
  13:00   8675309   Busy signal
  </code></pre>
  
  <p>I'm looking to generate a query in an Excel file (using PowerQuery) that will output something like this:</p>
  
  <pre><code>NUMBER    ATTEMPT START   ATTEMPT END   NUM. CALLS   STATUS
  5551234   10:00           11:00         2            Answered
  8675309   10:00           10:02         3            Answered
  8675309   12:00           13:00         4            Busy Signal
  </code></pre>
  
  <p>Here's the logic I'm trying to use:</p>
  
  <ul>
  <li>If no calls were answered, all calls on a single day are grouped under one attempt, with status Busy Signal.</li>
  <li>If a call was answered, that marks the end of an attempt. The attempt ended in this way will have status Answered. Future calls by the same number will start a new attempt.</li>
  <li>The start of an attempt is the time of the first call</li>
  <li>The end of an attempt is either the time of the call that was answered, or the last call made by that number that day</li>
  </ul>
  
  <p>I'm stuck on how to divide up the two attempts from <a href="https://www.youtube.com/watch?v=6WTdTwcmxyo" rel="nofollow noreferrer">Jenny</a>. It's fairly easy to write a query that will group all calls by one number into one attempt, but that would tell a misleading story. For example, in this case it would say she had one attempt starting at 10:00 and ending at 13:00, with a total of 7 calls. Whether it labeled that as "answered" or "busy signal" status, either would be misleading.</p>