Forum Discussion
Pivot table and date sorting/grouped by months/years when empty cells in selected data
Hi, I am just starting to use pivot table and I have to say it si so helpful! I am not able to group my date data per month/year. In my selected column of date I have many empty cells, this seems ...
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.