Google Sheets Query function missing data

Question

I have a raw data table that tracks events, which I use the query function in google sheets to arrange into a arranged table format. One of the raw data columns is the date that the event occurs. 99% of the time the query pulls the date correctly to the new table, although that 1% of the time it doesn't pull the date and leaves the cell blank.

I have tried formatting the entire column on both the raw data table and the new query generated table. Neither work.

=query(Input!A2:Q997,"select max(A), max(B), avg(C), sum(D), sum(I), max(L), N, max(O), min(P), max(Q) GROUP BY N ORDER BY N",0)

The Date is in column O, and it is ordered & grouped by a column N which is an 8 digit identifier number that can group certain events together (the reason for the query).

The rest of the table works fine mostly, although for some reason at times there are dates missing, it only seems to be dates, and its only on the small occasion, but unfortunately I have another query that is dependent on the results of the first query, which becomes skewed if the results of the first query have missing data.

player0 player0 · Accepted Answer · 2019-10-14T00:21:28

double-check the dates if they are valid. you can use ISDATE fx for that.

format input data internally as Plain Text

or try this:

=ARRAYFORMULA(QUERY(TO_TEXT(Input!A2:Q997),
 "select max(Col1),max(Col2),avg(Col3),sum(Col4),sum(Col9),max(Col12),Col14,max(Col15),min(Col16),max(Col17) 
  group by Col15 
  order by Col15", 0))

Google Sheets Query function missing data

1 Answers