Google Sheets query - How many tweets favourited by weekdays

Question

I'm having trouble with the formula below, hoping someone can help.

=query ({ arrayformula(if(tweets_zapier!A2:A<>"", weekday(tweets_zapier!A2:A),"")), tweets_zapier!L2:L}, "select Col1, count(Col2) where Col2 <> '' group by Col1 order by Col1 asc", 0)

The goal of this formula is to group the number of favourites by weekdays, to determine which weekday tweets are most favourited. Although the formula runs without any error, it doesn't provide any results.

I've included the link to my spreadsheet here (https://docs.google.com/spreadsheets/d/17CR_Q1TC6GSSkZCQwLprMqf7amkGH9yFZye4L_O18q4/edit#gid=1642780380)

Can anyone see what I'm missing?

Answer 1

Replace the where Col2 <> '' condition in the query by Col2 is not null. Not sure why it doesn't work but when you replace it, the query returns good results.

If you want to filter out the empty rows, you can also do it in the first query() argument (the arrayformula) by using filter().