google sheetsgoogle-sheets-query
I am relatively new to using the Query function but I would like to understand what I'm doing wrong in the following scenario.
I would like to pull from a table with a Year field in column F and Month field in column G. The query pulls correctly when filtering column E and J but returns nothing once I add either column F or G. This is weird because the source table contains a lot of data for the conditions (verified by filtering the source table). Any idea what could be going wrong?
Your task is hopeless because when only importing a single column there is no second column. However querying say Sheet1!A2:B3000 instead will still not work with COL2 because that is case sensitive, just like column b selection does not work in a query in place of B. Maybe try:
=QUERY(IMPORTRANGE("key","Sheet1!A2:B3000"),"SELECT * WHERE Col2='JB'")
I don't, except I suspect you are out of luck for the time being at least. However, even in Excel I have often found it as, or even more, useful to perform such grouping in the base data rather than in the pivot table. For me the summary provided by the table is often a prelude to drilling down for further detail and rather than end up then with many different extracts I find it much easier to filter the source data, which I have already formatted to suit me.
On that basis a hack of sorts may serve for you. Assuming data as in D:E in the image, I added three columns with formulae:
=year(D2) =month(D2)=weeknum(D2)To reduce the need for recalculation I usually then convert the results to values.
ColumnsA:C can then be included in the pivot table and, unlike Grouping, the components of the date can be split between rows and columns in the pivot table:
Best Answer
Get rid of the single quotes around concatenations for numbers; otherwise the QUERY is looking for strings.
Ex:
not
Same with
"and G ="