Forum Discussion
Pivot table and date sorting/grouped by months/years when empty cells in selected data
SergeiBaklan @@ My starting point is a normal table with dates. Not all the dates are showing. Since I wanted to create a Pivot table and then group dates, I wanted to have only blank cells and not "N/A" values, I used Power Query to replace all the "N/A" value in my table with an empty cell (I literally didn't put anything in) as you can see from the screenshot. Then I inserted a Pivot table from my table and the Pivot table looks like the screenshots (with many more rows). When I try to group it says "Cannot group that selection". There is an empty cell that is showing on 1st row and then a "(blank)" in last row
Excel doesn't return blank as a value. When you transform in Power Query "N/A" to nothing, Power Query actually returns empty to string to the grid. Thus you have combination of dates, blanks and texts ("" in your case). As soon as we have texts inside grouping doesn't work.
Alternatively you may load the table to data model, add calculated column in data model like
=IFERROR( DATEVALUE('Table2 2'[A] ), blank() )and build PivotTable from data model, not on table. Here we have only dates and blanks, grouping works.
- SergeiBaklan, thank you very much for your help, in the end I was able to group and solve it.