Convert blank values to zero when using the Query function

Question

Is there a way to convert data going into a query() function so that blank/null values are converted to zero?

I have a dataset with three columns:

Group    AC       Plan
Comms    350      [blank]
IT       50,000   85,000
Rent     15,000   15,000
Training [blank]  8,500

I want to query the set to find the difference between AC and Plan,
e.g. =query('data', "select A, B-C",1). However, since the query omits blank cells when calculating B-C, the result is like this:

Group    Diff
Comms    [blank]
IT       -35,000
Rent     0
Training [blank]

When I wanted this:

Group    Diff
Comms    350
IT       -35,000
Rent     0
Training -8,500

Before you go wild: I am not able to change the source data (the incoming data is actually another query() using the pivot functionality). And the reason I need this to work is I use a limit clause to confine the results to only the top 25 and bottom 25 differences.

score 6 · Accepted Answer · answered Feb 25 '19 at 16:13

6

=ARRAYFORMULA(IF(QUERY(T3:V7)="", 0, QUERY(T3:V7)))

answered Feb 25 '19 at 16:13

player0

124,011
12
67
124

1

Thanks, while this works, it is an expensive workaround given the processing that's needed for arrayformula(). Here I was hoping for an undocumented feature in the language but I guess we're not there yet. My sheet is fairly large and slow already and has a few of those queries. – a-burge Feb 26 '19 at 09:49
They definitely need the ability to coerce values. Adding the ability via the "options" clause seems the easiest for the users. Maybe adding a new clause like "coerce" could work, too?? – Christopher Rucinski Apr 25 '21 at 11:51
@ChristopherRucinski there isnt such parameter in google sheets like "coerce" - https://developers.google.com/chart/interactive/docs/querylanguage – player0 Apr 25 '21 at 18:21
Yeah, the current version is 0.7 ... Nothing says a newer version can't add functionality like that – Christopher Rucinski Apr 25 '21 at 18:24

score 0 · Answer 2 · answered Feb 18 '23 at 23:44

You can avoid ARRAYFORMULA by using MAP + LAMBDA functions. Looks shorter and faster with my large dataset in Sheets.

=MAP(your_query, LAMBDA(X, IF(X="", 0, X)))

Think of X as a math variable here. It represents any output from the first argument.

Convert blank values to zero when using the Query function

2 Answers2