title: select last non-null value and append it to another column BigQuery/PYTHON
body: <p>I have a table in BQ that looks like this:</p>
  <pre><code>Row     Field  DateTime  
  1        one   10:00 AM    
  2        null  10:05 AM     
  3        null  10:10 AM    
  4        one   10:30 AM    
  5        null  11:00 AM    
  6        two   11:15 AM    
  7        two   11:30 AM 
  8        null  11:35 AM
  9        null  11:40 AM
  10       null  11:50 AM
  11       null  12:00 AM
  12       null  12:15 AM
  13       two   12:30 AM
  14       null  12:15 AM
  15       null  12:25 AM
  16       null  12:35 AM
  17       three 12:55 AM     
  </code></pre>
  <p>I want to create another column called prevField and fill it out with the last Field value that is not null, when the first and last entry around the null are the same. When the first and last entry around null are different, it should remain null. The result would look like the following:</p>
  <pre><code>  Row     Field    DateTime  prevField
      1        one   10:00 AM   null 
      2        null  10:05 AM   one    
      3        null  10:10 AM   one   
      4        one   10:30 AM   one
      5        null  11:00 AM   null
      6        two   11:15 AM   two
      7        two   11:30 AM   two
      8        null  11:35 AM   two
      9        null  11:40 AM   two
      10       null  11:50 AM   two
      11       null  12:00 AM   two
      12       null  12:15 AM   two
      13       two   12:30 AM   two 
      14       null  12:15 AM   null
      15       null  12:15 AM   null
      16       null  12:15 AM   null
      17       three 12:15 AM   three  
  </code></pre>
  <p>So far i tried the following code variations for first part of the question (fill out prevField with the last Field value that is not null, when the first and last entry around the null are the same) but without success.</p>
  <pre><code>select Field, Datetime,
  
  (1)--case when FieldName is null then LAG(FieldName) over (order by DateTime) else FieldName end as prevFieldName
  
  (2)--LAST_VALUE(FieldName IGNORE NULLS) OVER (ORDER BY DateTime
  
  (3)--ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS prevFieldName
  
  (4)-- first_value(FieldName)over(order by DateTime) as prevFieldName  
  
  from table
  </code></pre>
  <p><strong>EDIT:</strong> I added rows to the data and change row numbers</p>