How to avoid this empty row using QUERY() in Google Sheets?

Question

I've tried applying some criteria, but the row is still there.

This first empty row shouldn't be there.

Here is the formula, in case you'd like to help me correct it: =if(I4<>"", { IFERROR(query(Datasets!X3:AU,"select AS, AT, AJ, AR where AS matches date '"&TEXT(DATEVALUE(I4),"yyyy-mm-dd")&"'",0),{"","","",""}); IFERROR(query(Datasets!BC3:BT,"select BC, BD, BS, BQ where BC matches date '"&TEXT(DATEVALUE(I4),"yyyy-mm-dd")&"'",0),{"","","",""}) }, UNIQUE({query(Datasets!X3:AU,"select AS, AT, ' ', SUM(AR) where AS is not null group by AS, AT, ' ' label ' '' ', SUM(AR) ' '",0); query(Datasets!BC3:BT,"select BC, BD, ' ', SUM(BQ) where BC is not null group by BC, BD, ' ' label ' '' ', SUM(BQ) ' '",0) }))

remove the spaces in the labels. label x ' ' -> label x '' or use query(formula,"offset 1",0) — z..
– z.., Commented Dec 24, 2022 at 20:46
I'll try that, @ztiaa! Thanks for your comment and congrats on your website. — onit
– onit, Commented Dec 26, 2022 at 13:27

player0 · Accepted Answer · 2022-12-24 21:26:11Z

1

try:

=IF(I4<>"", {IFERROR(QUERY(Datasets!X3:AU, 
 "select AS,AT,AJ,AR 
  where AS matches date '"&TEXT(DATEVALUE(I4), "yyyy-mm-dd")&"'", ), {"","","",""}); 
 IFERROR(QUERY(Datasets!BC3:BT, 
 "select BC,BD,BS,BQ 
  where BC matches date '"&TEXT(DATEVALUE(I4), "yyyy-mm-dd")&"'", ), {"","","",""})}, 
 UNIQUE({QUERY(Datasets!X3:AU,
 "select AS,AT,' ',SUM(AR) 
  where AS is not null 
  group by AS,AT,' '
  label' ''',SUM(AR)''", ); 
 QUERY(Datasets!BC3:BT, 
 "select BC,BD,' ',SUM(BQ) 
  where BC is not null 
  group by BC,BD,' ' 
  label' ''',SUM(BQ)''", )}))

answered Dec 24, 2022 at 21:26

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

onit Over a year ago

That actually didn't solve it. Couldnt see what changed in the formula... Thanks for your answer.

onit Over a year ago

i've wrapped the QUERY()'s in a new QUERY() and it worked. Thanks a lot!

Collectives™ on Stack Overflow

How to avoid this empty row using QUERY() in Google Sheets?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related