Grouping by date disabled in pivot table

[sharshra]

I have a pivot table where I'm trying to group by week. But dates are disabled in grouping.

Tried all options suggested for similar issue in this forum & elsewhere in the internet. All dates are in date format, but still dates are disabled in grouping. Not sure what is going wrong. Any suggestions?

 

[Alex Blakenburg]

Sorry we didn't really get to the bottom of it for you. Glad you have been able create a new pivot table that worked.

 

[sharshra]

No problems at all, Alex. Thanks for all suggestions so far.

 

[SpillerBD]

and I see where you can have data keep the Group By from happening.
Right Click the Pivot Table and select the Pivot Table Options. Select the Data tab in the pop-up window.
Note the setting for "Number of items to retain per field:" If set to Automatic or Max, old bad formatted data that isn't even in current use will be retained for filters/slicers and is part of the evaluation if a field can be grouped.
Change the "Number of items to retain per field:" to "None" and then Refresh your Pivot Table Data.

 

[sharshra]

'Number of items to retain per field' is also disabled & doesn't allow to change to 'None'.

 

[Alex Blakenburg]

If you click on Power Pivot > Manage, do you get a blank screen or do you see a table with data ?

Also with the offending pivot selected can you show me your Fields panel and does it show a hierarchy with the group heading having an icon that looks like a table - see picture below ?

I know we have talked about this before but I can only reproduce both the symptoms you have described if I use Power Pivot and the Data model.

 

[sharshra]

I can see the tables in power pivot manage option & I want to continue them as is for other purposes. The pivot table having the problem refers to the same table which is seen in power pivot manage option. Will that be a problem?

I see the field panel, similar to the one shown post 15. But I can't paste the screenshot due to data sensitivity & restrictions.

 

[Alex Blakenburg]

That's fine but it means that Pivot Table is not using the Standard Pivot Table function but the Power Pivot function.
Power Pivot does not give you all the same grouping options using the standard grouping dialogue box nor does it need the ability to change the "Retain items deleted...."

If you use dates in Power Pivot you should be setting up a calendar table in the Data Model and setting up any grouping either in that and / or by using DAX measures and formulas.

 

[sharshra]

I have to group by weeks & display the trend. Wouldn't require power pivot for that. I have achieved this by creating a new pivot table. But curious to know if it is possible to change from power pivot to normal pivot, without having to create a new normal pivot table.

 

[Alex Blakenburg]

But curious to know if it is possible to change from power pivot to normal pivot, without having to create a new normal pivot table.

Interesting question. I don't believe it is possible through the excel interface.

You might be able to do it via VBA if the table in the Data Model is imported directly from an Excel Table in the workbook and none of the column names in the Data Model table have been changed and the Data Model table still has the original table name or you supply the original table name.
VBA would then have the information to rebuild the table using the standard table.

Probably not worth the effort though.

 

[sharshra]

Agreed. It was easy for me to create a simple normal pivot table than investigating further. I just created a new pivot, no VBA & all.