Here is our editorial take on the situation described.
This pivot table problem isn't a bug. It's a feature, and one that tells you exactly where Microsoft Excel draws a line between its traditional engine and its modern data model. The user's experience is frustratingly common: the same query, the same dates, two different behaviors. Export to a table first, and grouping by month works like a charm. Load directly into the data model, and the group field option vanishes. The culprit is hiding in plain sight: the data model treats dates as distinct values, not as a continuous timeline you can slice into months, quarters, or years. The user's own workaround, wrapping the date in a TEXT function, confirms it. The pivot table from the data model sees a string, not a date hierarchy.
This matters because the data model is supposed to be the upgrade path. It handles millions of rows. It connects multiple tables. It's the engine behind Power Pivot and modern Excel analytics. Yet here, it breaks a basic, decades-old feature that a simple table handles without complaint. The user rightly suspects the data model is the reason. They are correct. When you load data directly into the data model, Excel no longer uses the familiar pivot table cache. It uses a column store that stores dates as flat values. Grouping in a traditional pivot table relies on the cache holding a contiguous range of dates. The data model doesn't do that. It stores every date as an individual, unsorted key. The left indent the user noticed on the non-working pivot table is the classic sign: the field is being treated as text, not as a date hierarchy.
Our opinion is plain: Microsoft should have fixed this years ago. The data model is powerful, but it should not make basic date grouping a treasure hunt of workarounds. For now, the practical path is clear. If you need to group dates by month, do not load your Power Query output directly into a pivot table. Export it to a regular table first, then build your pivot on that table. It adds one step, but it preserves the date intelligence you need. Alternatively, add a calculated column in Power Query that extracts the month and year as a text field. That gives you a manual grouping column that works inside the data model. Neither solution is elegant, but both are reliable. The user found the right fix with TEXT; the lesson is that the data model demands you do the grouping at the query stage, not the pivot stage.