Pivot table and date sorting/grouped by months/years when empty cells in selected data

[Emilie_BRP]

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 to be my issue, nevertheless I cannot remove all of them or even sort this column.

I wanted to know if it was possible to group my dates by month and years even with these empty cells? For exemple, can I replace the empty cells by something else using a formula or anything else?

Will be happy to read your suggestions/advices/solutions

Thank you very much,

Best,

Emilie

[#2]

Yes,
Step 1 - Split your Date Column like this
15-March-2022 | Year(Date) | Text(Date, "MMM")
So you should have an helper column in years and Months
Then Load the table to Pivot Table > Drag the Month Field to Rows and Years To Column Fields and Amount to Values Field.

Does your formula for the dates have blanks? For whatever reason, the pivot recognizes a true blank cell differently than a formula that returns "". True blanks don't affect grouping but the formula ones will not allow you to group.

You could try using PowerQuery to do the data cleaning and then your output will have true blanks and thus be able to be manipulated & grouped correctly through a pivot.

[#3]

@Emilie_BRP 

Is that like this?

[#4]

@Donald_Genes_ 

Hi,

thank you very much for your response, 

Yes, I have formula and not real blanks, maybe it is my problem, let me try that first.

Power query, don't know this. I have to check if I have it and how it works to put real blanks

[#5]

I am sorry, I don't really understand your question, like this how?
the data table on the left or the pivot tables on the right?

Thx

[#6]

@Emilie_BRP were you able to solve? I have the same issue.

[#7]

@Donald_Genes_ All my blank cells are "true blank", I checked with the function Isblank(), but still not able to group dates. Do you have any suggestions? Thank you in advance

[#8]

@alexcerci 

Do you have dates (which are actually numbers) or texts which looks like dates? You may check by ISNUMBER(). Also, do you have any other texts in Date column?

[#9]

@Sergei Baklan I have the date column formatted as date. When I insert a pivot table and try to group the dates it tells me the error "cannot group...". I attach here a similar scenario.

[#10]

@alexcerci 

It doesn't matter how Date column is formatted. If you apply date or number format to the text, it still will be a text. Formatting doesn't convert the value.

Here is the sample with dates (=ISNUMBER() returns TRUE) and texts which looks like dates

Again, please be sure you have dates, not texts.

[#11]

@Sergei Baklan the cells which contain dates are numbers, the blank cells are not, I checked with ISNUMBER(). The problem is that whatever method I use to convert the blank cells to numbers they are not working, if I use the ISNUMBER() it keeps telling me FALSE.

[#12]

@alexcerci 

Okay, thank you. That's not necessary to check blanks, they are shown in PivotTable.

Not sure that else could be without the file. If ypu are not able to share your sample, perhaps you may check if attached works in your environment.

[#13]

@Sergei Baklan @@ 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

[#14]

@alexcerci 

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.

[#15]

@Sergei Baklan, thank you very much for your help, in the end I was able to group and solve it.