Column D has order references like 4471 and A-4472 mixed together. =QUERY(A:F, "select A, D where B = 'open'") returns the numeric ones fine and leaves blanks where the text ones should be. If I sort the sheet so text is at the top it flips and drops the numbers instead. Roughly 3000 rows. What is QUERY actually doing here?
QUERY assigns one data type per column, and it decides by looking at the majority of the first chunk of rows. Whichever type loses gets returned as blank, not converted. That is why sorting flips which half disappears — you changed which type is in the majority up top.
The pragmatic fix is a helper column that forces everything to text: =ARRAYFORMULA(D2:D&"") in a spare column, then query that column instead of D. Ugly, reliable, done in thirty seconds.
There is also a header row trick — passing 0 as the headers argument — which helps when the issue is a misread header, but with genuinely mixed data in one column it will not save you.
Helper column with &"" fixed it in about thirty seconds after two hours of trying to make QUERY behave. Thank you.
Worth knowing it only samples the first few hundred rows to decide, which is why a sheet can work fine for months and then break when someone adds a batch of data at the top.
The underlying issue is that an order reference is not really a number, it is an identifier that sometimes looks like one. If you can, format the whole column as plain text at source so every value is consistently text. Mixed-type columns cause trouble in lookups, pivots and sorting too, not just here.
One thing to watch with the helper column approach: numbers converted to text sort as text downstream, so 10 comes before 9. Fine for reference codes, painful if anyone later tries to sort by it expecting numeric order.
@till_and_tally · 2mo ago · 2 replies
If you have the newer functions,
FILTERdoes not have this problem at all because it does not impose a column type.=FILTER({A:A, D:D}, B:B = "open")gives you both halves. You lose QUERY's sorting and grouping, but for a straight filter it is simpler and more predictable.Reply
Report
@listcomp_leo · 2mo ago
Good call, and I reach for FILTER first these days for exactly this reason. QUERY earns its keep on group by and pivot, not on plain where clauses.
Reply
Report