How do I create a pivot table group in Google Sheets when the ungrouped values produce too many columns? Right click on the columns is not available

Question

I have a Google spreadsheet for expenses: one column for the YYYY-MM-DD date of the expense, one column for the category, and one column for the amount. This sheet has thousands of rows. The goal is a pivot table with MM-YYYY in the column, summing amounts by the month/year.

Usually, that's easy. I create the pivot table with the ungrouped variable in the column, and then right click and create a pivot table group. However, when I create a pivot table with the date as the column of the table, Google Sheets says

This pivot table is trying to produce too many columns. Make it smaller by adjusting the column breakouts or filtering the source data.

There isn't a column to right click on to create the group because Google Sheets couldn't produce any of the columns (see screenshot)

How do I create a pivot table group here? Is the only to create another column in the sheet with the raw data (I'd prefer not to do this because that sheet is an output from another program) that does the grouping with a formula?

Answer 1

The easiest solution is to add a very narrow filter, for example to show data for only one date:

And then you will have one column with the date to right click on and and pick the MM-YYYY format. After that you can remove this filter and all the data will be there, grouped in the selected format.

Answer 2

There are two options:

1 - Create a query, then create a Pivot table based on the query
2 - Create a query that includes a pivot table

Query then Pivot

Query - Use this formula:

=query({arrayformula(text(TO_DATE(DATEVALUE(A3:A81)),"YYYY-mm")),B3:C81},"select Col1, Col2, sum(Col3) group by Col1, Col2 label Col1 'Date',Col2 'Category',Sum(Col3) 'Total'")

---

Pivot Table

---

Query with Pivot

Use this formula:
={query({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80},"select Col1, sum(Col3) group by Col1 pivot Col2 label Col1 'Date',Sum(Col3) 'Total'"),QUERY({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}, "select sum(Col3) group by Col1 label sum(Col3) 'Month Total'");TRANSPOSE(QUERY({{arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}}, "select sum(Col3) group by Col2 label sum(Col3) 'Total'")),query({QUERY({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}, "select sum(Col3) group by Col1 label sum(Col3) 'Month Total'")},"select sum(Col1) label sum(Col1) ''")}

---

Screenshot

---

Sample data