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 ...
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.
Resources
"}},"componentScriptGroups({\"componentId\":\"custom.widget.MicrosoftFooter\"})":{"__typename":"ComponentScriptGroups","scriptGroups":{"__typename":"ComponentScriptGroupsDefinition","afterInteractive":{"__typename":"PageScriptGroupDefinition","group":"AFTER_INTERACTIVE","scriptIds":[]},"lazyOnLoad":{"__typename":"PageScriptGroupDefinition","group":"LAZY_ON_LOAD","scriptIds":[]}},"componentScripts":[]},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/community/NavbarDropdownToggle\"]})":[{"__ref":"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/QueryHandler\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/QueryHandler-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/EscalatedMessageBanner\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/EscalatedMessageBanner-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/users/UserLink\"]})":[{"__ref":"CachedAsset:text:en_US-components/users/UserLink-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserRank\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserRank-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageTime\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageTime-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSolvedBadge\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSolvedBadge-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSubject\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSubject-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageBody\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageBody-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageCustomFields\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageCustomFields-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageReplyButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageReplyButton-1745505309826"}],"message({\"id\":\"message:3257367\"})":{"__ref":"ForumTopicMessage:message:3257367"},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserAvatar\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/ranks/UserRankLabel\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/AcceptedSolutionButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/ThreadedReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/ThreadedReplyList-1745505309826"}],"message({\"id\":\"message:4020298\"})":{"__ref":"ForumReplyMessage:message:4020298"},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/tags/TagView/TagViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309826"}],"message({\"id\":\"message:4023569\"})":{"__ref":"ForumReplyMessage:message:4023569"},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/LinearReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505309826"}],"message({\"id\":\"message:3606023\"})":{"__ref":"ForumReplyMessage:message:3606023"},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/attachments/AttachmentView/AttachmentViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309826"}],"message({\"id\":\"message:3617475\"})":{"__ref":"ForumReplyMessage:message:3617475"}},"CachedAsset:pages-1745484442433":{"__typename":"CachedAsset","id":"pages-1745484442433","value":[{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogViewAllPostsPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId/all-posts/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CasePortalPage","type":"CASE_PORTAL","urlPath":"/caseportal","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CreateGroupHubPage","type":"GROUP_HUB","urlPath":"/groups/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CaseViewPage","type":"CASE_DETAILS","urlPath":"/case/:caseId/:caseNumber","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"InboxPage","type":"COMMUNITY","urlPath":"/inbox","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"HelpFAQPage","type":"COMMUNITY","urlPath":"/help","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaMessagePage","type":"IDEA_POST","urlPath":"/idea/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaViewAllIdeasPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/all-ideas/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"LoginPage","type":"USER","urlPath":"/signin","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogPostPage","type":"BLOG","urlPath":"/category/:categoryId/blogs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserBlogPermissions.Page","type":"COMMUNITY","urlPath":"/c/user-blog-permissions/page","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ThemeEditorPage","type":"COMMUNITY","urlPath":"/designer/themes","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbViewAllArticlesPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId/all-articles/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllEvents","type":"CUSTOM","urlPath":"/Events","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionEditPage","type":"EVENT","urlPath":"/event/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OAuthAuthorizationAllowPage","type":"USER","urlPath":"/auth/authorize/allow","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"PageEditorPage","type":"COMMUNITY","urlPath":"/designer/pages","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"PostPage","type":"COMMUNITY","urlPath":"/category/:categoryId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumBoardPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbBoardPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"EventPostPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserBadgesPage","type":"COMMUNITY","urlPath":"/users/:login/:userId/badges","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubMembershipAction","type":"GROUP_HUB","urlPath":"/membership/join/:nodeId/:membershipType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"MaintenancePage","type":"COMMUNITY","urlPath":"/maintenance","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaReplyPage","type":"IDEA_REPLY","urlPath":"/idea/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserSettingsPage","type":"USER","urlPath":"/mysettings/:userSettingsTab","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubsPage","type":"GROUP_HUB","urlPath":"/groups","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumPostPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionRsvpActionPage","type":"OCCASION","urlPath":"/event/:boardId/:messageSubject/:messageId/rsvp/:responseType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"VerifyUserEmailPage","type":"USER","urlPath":"/verifyemail/:userId/:verifyEmailToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"AllOccasionsPage","type":"OCCASION","urlPath":"/category/:categoryId/events/:boardId/all-events/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"EventBoardPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbReplyPage","type":"TKB_REPLY","urlPath":"/kb/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaBoardPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CommunityGuideLinesPage","type":"COMMUNITY","urlPath":"/communityguidelines","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CaseCreatePage","type":"SALESFORCE_CASE_CREATION","urlPath":"/caseportal/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbEditPage","type":"TKB","urlPath":"/kb/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForgotPasswordPage","type":"USER","urlPath":"/forgotpassword","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaEditPage","type":"IDEA","urlPath":"/idea/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TagPage","type":"COMMUNITY","urlPath":"/tag/:tagName","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogBoardPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionMessagePage","type":"OCCASION_TOPIC","urlPath":"/event/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ManageContentPage","type":"COMMUNITY","urlPath":"/managecontent","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ClosedMembershipNodeNonMembersPage","type":"GROUP_HUB","urlPath":"/closedgroup/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CommunityPage","type":"COMMUNITY","urlPath":"/","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumMessagePage","type":"FORUM_TOPIC","urlPath":"/discussions/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaPostPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"CommunityHub.Page","type":"CUSTOM","urlPath":"/Directory","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogMessagePage","type":"BLOG_ARTICLE","urlPath":"/blog/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"RegistrationPage","type":"USER","urlPath":"/register","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"EditGroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumEditPage","type":"FORUM","urlPath":"/discussions/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ResetPasswordPage","type":"USER","urlPath":"/resetpassword/:userId/:resetPasswordToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllBlogs.Page","type":"CUSTOM","urlPath":"/blogs","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbMessagePage","type":"TKB_ARTICLE","urlPath":"/kb/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogEditPage","type":"BLOG","urlPath":"/blog/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ManageUsersPage","type":"USER","urlPath":"/users/manage/:tab?/:manageUsersTab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumReplyPage","type":"FORUM_REPLY","urlPath":"/discussions/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"PrivacyPolicyPage","type":"COMMUNITY","urlPath":"/privacypolicy","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"NotificationPage","type":"COMMUNITY","urlPath":"/notifications","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserPage","type":"USER","urlPath":"/users/:login/:userId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionReplyPage","type":"OCCASION_REPLY","urlPath":"/event/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ManageMembersPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/manage/:tab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"SearchResultsPage","type":"COMMUNITY","urlPath":"/search","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogReplyPage","type":"BLOG_REPLY","urlPath":"/blog/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TermsOfServicePage","type":"COMMUNITY","urlPath":"/termsofservice","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CategoryPage","type":"CATEGORY","urlPath":"/category/:categoryId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumViewAllTopicsPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/all-topics/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbPostPage","type":"TKB","urlPath":"/category/:categoryId/kbs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubPostPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"}],"localOverride":false},"CachedAsset:text:en_US-components/context/AppContext/AppContextProvider-0":{"__typename":"CachedAsset","id":"text:en_US-components/context/AppContext/AppContextProvider-0","value":{"noCommunity":"Cannot find community","noUser":"Cannot find current user","noNode":"Cannot find node with id {nodeId}","noMessage":"Cannot find message with id {messageId}"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Loading/LoadingDot-0":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Loading/LoadingDot-0","value":{"title":"Loading..."},"localOverride":false},"User:user:-1":{"__typename":"User","id":"user:-1","uid":-1,"login":"Deleted","email":"","avatar":null,"rank":null,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":"ANONYMOUS","registrationTime":null,"confirmEmailStatus":false,"registrationAccessLevel":"VIEW","ssoRegistrationFields":[]},"ssoId":null,"profileSettings":{"__typename":"ProfileSettings","dateDisplayStyle":{"__typename":"InheritableStringSettingWithPossibleValues","key":"layout.friendly_dates_enabled","value":"false","localValue":"true","possibleValues":["true","false"]},"dateDisplayFormat":{"__typename":"InheritableStringSetting","key":"layout.format_pattern_date","value":"MMM dd yyyy","localValue":"MM-dd-yyyy"},"language":{"__typename":"InheritableStringSettingWithPossibleValues","key":"profile.language","value":"en-US","localValue":"en","possibleValues":["en-US"]}},"deleted":false},"Theme:customTheme1":{"__typename":"Theme","id":"customTheme1"},"Category:category:microsoft365":{"__typename":"Category","id":"category:microsoft365","entityType":"CATEGORY","displayId":"microsoft365","nodeType":"category","depth":3,"title":"Microsoft 365","shortTitle":"Microsoft 365","parent":{"__ref":"Category:category:products-services"},"categoryPolicies":{"__typename":"CategoryPolicies","canReadNode":{"__typename":"PolicyResult","failureReason":null}}},"Category:category:top":{"__typename":"Category","id":"category:top","displayId":"top","nodeType":"category","depth":0,"title":"Top","entityType":"CATEGORY","shortTitle":"Top"},"Category:category:communities":{"__typename":"Category","id":"category:communities","displayId":"communities","nodeType":"category","depth":1,"parent":{"__ref":"Category:category:top"},"title":"Communities","entityType":"CATEGORY","shortTitle":"Communities"},"Category:category:products-services":{"__typename":"Category","id":"category:products-services","displayId":"products-services","nodeType":"category","depth":2,"parent":{"__ref":"Category:category:communities"},"title":"Products","entityType":"CATEGORY","shortTitle":"Products"},"Forum:board:ExcelGeneral":{"__typename":"Forum","id":"board:ExcelGeneral","entityType":"FORUM","displayId":"ExcelGeneral","nodeType":"board","depth":4,"conversationStyle":"FORUM","title":"Excel","description":"Your community for how-to discussions and sharing best practices on Microsoft Excel. If you’re looking for technical support, please visit Microsoft Support Community.","avatar":null,"profileSettings":{"__typename":"ProfileSettings","language":null},"parent":{"__ref":"Category:category:microsoft365"},"ancestors":{"__typename":"CoreNodeConnection","edges":[{"__typename":"CoreNodeEdge","node":{"__ref":"Community:community:gxcuf89792"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:communities"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:products-services"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:microsoft365"}}]},"userContext":{"__typename":"NodeUserContext","canAddAttachments":false,"canUpdateNode":false,"canPostMessages":false,"isSubscribed":false},"boardPolicies":{"__typename":"BoardPolicies","canPublishArticleOnCreate":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","key":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","args":[]}}},"shortTitle":"Excel","repliesProperties":{"__typename":"RepliesProperties","sortOrder":"REVERSE_PUBLISH_TIME","repliesFormat":"threaded"},"tagProperties":{"__typename":"TagNodeProperties","tagsEnabled":{"__typename":"PolicyResult","failureReason":null}},"requireTags":true,"tagType":"PRESET_ONLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/"},"Rank:rank:37":{"__typename":"Rank","id":"rank:37","position":18,"name":"Copper Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"User:user:1333522":{"__typename":"User","id":"user:1333522","uid":1333522,"login":"Emilie_BRP","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-5.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"email":"","messagesCount":3,"biography":null,"topicsCount":1,"kudosReceivedCount":0,"kudosGivenCount":0,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2022-03-15T03:40:28.929-07:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0},"ForumTopicMessage:message:3257367":{"__typename":"ForumTopicMessage","uid":3257367,"subject":"Pivot table and date sorting/grouped by months/years when empty cells in selected data","id":"message:3257367","revisionNum":1,"repliesCount":14,"author":{"__ref":"User:user:1333522"},"depth":0,"hasGivenKudo":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3257367"},"readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3257367"},"body@stripHtml({\"truncateLength\":200})":" 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 ...","body@stringLength":"634","rawBody":"
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
","kudosSumWeight":0,"postTime":"2022-03-15T03:58:04.853-07:00","images":{"__typename":"AssociatedImageConnection","edges":[],"totalCount":0,"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"attachments":{"__typename":"AttachmentConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"tags":{"__typename":"TagConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"TagEdge","cursor":"MjUuMXwyLjF8b3wxMHxfTlZffDE","node":{"__typename":"Tag","id":"tag:excel","text":"excel","time":"2016-06-21T14:08:51.129-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}}]},"timeToRead":1,"currentRevision":{"__ref":"Revision:revision:3257367_1"},"latestVersion":null,"metrics":{"__typename":"MessageMetrics","views":22872},"visibilityScope":"PUBLIC","canonicalUrl":null,"seoTitle":null,"seoDescription":null,"isEscalated":null,"placeholder":false,"originalMessageForPlaceholder":null,"messagePolicies":{"__typename":"MessagePolicies","canModerateSpamMessage":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","key":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","args":[]}}},"archivalData":null,"customFields":[]},"Conversation:conversation:3257367":{"__typename":"Conversation","id":"conversation:3257367","solved":false,"topic":{"__ref":"ForumTopicMessage:message:3257367"},"lastPostingActivityTime":"2022-09-05T03:43:41.508-07:00","lastPostTime":"2022-09-05T03:43:41.508-07:00","unreadReplyCount":14,"isSubscribed":false},"ModerationData:moderation_data:3257367":{"__typename":"ModerationData","id":"moderation_data:3257367","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"Revision:revision:3257367_1":{"__typename":"Revision","id":"revision:3257367_1","lastEditTime":"2022-03-15T03:58:04.853-07:00"},"ForumReplyMessage:message:3606371":{"__typename":"ForumReplyMessage","id":"message:3606371","conversation":{"__ref":"Conversation:conversation:3257367"},"author":{"__ref":"User:user:521"},"revisionNum":1,"uid":3606371,"depth":9,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"subject":"Re: Pivot table and date sorting/grouped by months/years when empty cells in selected data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3606371"},"parent":{"__ref":"ForumReplyMessage:message:3606023"},"body":"
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.
\n
Alternatively you may load the table to data model, add calculated column in data model like
\n
=IFERROR( DATEVALUE('Table2 2'[A] ), blank() )
\n
and build PivotTable from data model, not on table. Here we have only dates and blanks, grouping works.
\n
","body@stringLength":"1245","rawBody":"
\n
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.
\n
Alternatively you may load the table to data model, add calculated column in data model like
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.
\n
Here is the sample with dates (=ISNUMBER() returns TRUE) and texts which looks like dates
\n
\n
Again, please be sure you have dates, not texts.
","body@stringLength":"939","rawBody":"
\n
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.
\n
Here is the sample with dates (=ISNUMBER() returns TRUE) and texts which looks like dates
\n
\n
Again, please be sure you have dates, not texts.
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2022-08-21T03:34:31.023-07:00"},"ForumReplyMessage:message:3605335":{"__typename":"ForumReplyMessage","id":"message:3605335","revisionNum":2,"uid":3605335,"depth":6,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3257367"},"subject":"Re: Pivot table and date sorting/grouped by months/years when empty cells in selected data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3605335"},"body":"
SergeiBaklan 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.
","body@stringLength":"462","rawBody":"
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.
","author":{"__ref":"User:user:1492584"},"isEscalated":null,"postTime":"2022-08-21T10:18:53.985-07:00","parent":{"__ref":"ForumReplyMessage:message:3605241"}},"ForumReplyMessage:message:3605379":{"__typename":"ForumReplyMessage","id":"message:3605379","revisionNum":1,"uid":3605379,"depth":7,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3257367"},"subject":"Re: Pivot table and date sorting/grouped by months/years when empty cells in selected data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3605379"},"body":"
Okay, thank you. That's not necessary to check blanks, they are shown in PivotTable.
\n
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.
","body@stringLength":"461","rawBody":"
\n
Okay, thank you. That's not necessary to check blanks, they are shown in PivotTable.
\n
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.
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2022-08-21T12:10:28.354-07:00","parent":{"__ref":"ForumReplyMessage:message:3605335"}},"ForumReplyMessage:message:3606023":{"__typename":"ForumReplyMessage","id":"message:3606023","revisionNum":1,"uid":3606023,"depth":8,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3257367"},"subject":"Re: Pivot table and date sorting/grouped by months/years when empty cells in selected data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3606023"},"body":"
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
","body@stringLength":"823","rawBody":"
@@ 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
","author":{"__ref":"User:user:1492584"},"isEscalated":null,"postTime":"2022-08-22T07:18:31.177-07:00","parent":{"__ref":"ForumReplyMessage:message:3605379"},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[{"__typename":"AttachmentEdge","cursor":"MjUuMXwyLjF8b3w1fF9OVl98MQ","node":{"__ref":"Attachment:{\"id\":\"attachment:message3606023AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/159968/1/excel%202.PNG\"}"}},{"__typename":"AttachmentEdge","cursor":"MjUuMXwyLjF8b3w1fF9OVl98Mg","node":{"__ref":"Attachment:{\"id\":\"attachment:message3606023AttachmentNumber2\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/159968/2/screen%20excel.PNG\"}"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"AssociatedImage:{\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS0zNjA2MzcxLTM5NzkyMWlENTY0NTUyRTdFMTBCNjI5?revision=1\"}":{"__typename":"AssociatedImage","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS0zNjA2MzcxLTM5NzkyMWlENTY0NTUyRTdFMTBCNjI5?revision=1","title":"image.png","associationType":"BODY","width":441,"height":255,"altText":null},"Attachment:{\"id\":\"attachment:message3606371AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/160018/1/PT%20empty%20and%20blank.xlsx\"}":{"__typename":"Attachment","id":"attachment:message3606371AttachmentNumber1","filename":"PT empty and blank.xlsx","filesize":129894,"contentType":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/160018/1/PT%20empty%20and%20blank.xlsx"},"Revision:revision:3606371_1":{"__typename":"Revision","id":"revision:3606371_1","lastEditTime":"2022-08-22T12:21:29.087-07:00"},"QueryVariables:ReplyList:message:4020298:1":{"__typename":"QueryVariables","id":"ReplyList:message:4020298:1","value":{"id":"message:4020298","first":10,"sorts":{"postTime":{"direction":"DESC"}},"repliesFirst":3,"repliesFirstDepthThree":1,"repliesSorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":true,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":false,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"ROOT_MUTATION":{"__typename":"Mutation"},"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1745505309826","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1745505309826","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1745505309826","value":{"accept":"Mark as Solution","accepted":"Marked as Solution","errorHeader":"Error!","errorAdd":"There was an error marking as solution.","errorRemove":"There was an error unmarking as solution.","solved":"Solved"},"localOverride":false},"CachedAsset:text:en_US-components/messages/ThreadedReplyList-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/messages/ThreadedReplyList-1745505309826","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"ForumReplyMessage:message:4020298":{"__typename":"ForumReplyMessage","id":"message:4020298","customFields":[],"revisionNum":1,"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"uid":4020298,"author":{"__ref":"User:user:2095049"},"readOnly":false,"repliesCount":1,"parent":{"__ref":"ForumTopicMessage:message:4020242"},"replies":{"__typename":"MessageConnection","edges":[{"__typename":"MessageEdge","cursor":"MjUuMXwyLjF8aXwxMHwxMzI6MHxpbnQsNDAyMzU2OSw0MDIzNTY5","node":{"__ref":"ForumReplyMessage:message:4023569"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"User:user:2228857":{"__typename":"User","id":"user:2228857","deleted":false,"uid":2228857,"login":"phimmychan35","biography":null,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2024-01-03T01:08:31.225-08:00"},"email":"","avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-4.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"entityType":"USER","eventPath":"community:gxcuf89792/user:2228857"},"ForumTopicMessage:message:4020242":{"__typename":"ForumTopicMessage","uid":4020242,"id":"message:4020242","revisionNum":1,"author":{"__ref":"User:user:2228857"},"readOnly":false,"repliesCount":8,"subject":"Matching help"},"User:user:2095049":{"__typename":"User","id":"user:2095049","deleted":false,"uid":2095049,"login":"djclements","biography":null,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2023-10-21T02:08:27.156-07:00"}},"Conversation:conversation:4020242":{"__typename":"Conversation","id":"conversation:4020242","solved":true,"topic":{"__ref":"ForumTopicMessage:message:4020242"},"lastPostingActivityTime":"2024-01-08T06:06:35.614-08:00","lastPostTime":"2024-01-08T06:06:35.614-08:00","unreadReplyCount":8},"ModerationData:moderation_data:4023569":{"__typename":"ModerationData","id":"moderation_data:4023569","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4023569":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:2228857"},"id":"message:4023569","revisionNum":1,"uid":4023569,"depth":2,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4020298"},"conversation":{"__ref":"Conversation:conversation:4020242"},"subject":"Re: Matching help","moderationData":{"__ref":"ModerationData:moderation_data:4023569"},"body":"Thank you so much. I will check this out!","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"41","kudosSumWeight":0,"repliesCount":0,"postTime":"2024-01-07T20:23:39.562-08:00","lastPublishTime":"2024-01-07T20:23:39.562-08:00","metrics":{"__typename":"MessageMetrics","views":1707},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:4020242/message:4023569","replies":{"__typename":"MessageConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1745505309826","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false},"QueryVariables:ReplyList:message:3606371:1":{"__typename":"QueryVariables","id":"ReplyList:message:3606371:1","value":{"id":"message:3606371","sorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":false,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":false,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/messages/LinearReplyList-1745505309826","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"ModerationData:moderation_data:3617475":{"__typename":"ModerationData","id":"moderation_data:3617475","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:3617475":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:1492584"},"id":"message:3617475","revisionNum":1,"uid":3617475,"depth":10,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:3606371"},"conversation":{"__ref":"Conversation:conversation:3257367"},"subject":"Re: Pivot table and date sorting/grouped by months/years when empty cells in selected data","moderationData":{"__ref":"ModerationData:moderation_data:3617475"},"body":"SergeiBaklan, thank you very much for your help, in the end I was able to group and solve it.","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"93","kudosSumWeight":1,"postTime":"2022-09-05T03:43:41.508-07:00","lastPublishTime":"2022-09-05T03:43:41.508-07:00","metrics":{"__typename":"MessageMetrics","views":19830},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:3257367/message:3617475","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"Attachment:{\"id\":\"attachment:message3606023AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/159968/1/excel%202.PNG\"}":{"__typename":"Attachment","id":"attachment:message3606023AttachmentNumber1","filename":"excel 2.PNG","filesize":10127,"contentType":"image/png","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/159968/1/excel%202.PNG"},"Attachment:{\"id\":\"attachment:message3606023AttachmentNumber2\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/159968/2/screen%20excel.PNG\"}":{"__typename":"Attachment","id":"attachment:message3606023AttachmentNumber2","filename":"screen excel.PNG","filesize":7142,"contentType":"image/png","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/159968/2/screen%20excel.PNG"},"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309826","value":{"errorTitle":"Failed!","previewFile":"Preview File","downloadFile":"Download File {name}","removeFile":"Remove File {name}","errorBadExtension":"This file does not have a valid extension. \"{extensions}\" are the valid extensions.","errorFileEmpty":"This file is empty or does not exist.","errorTooLarge":"The maximum file size is: {maxFileSize}.","errorTooMany":"Too many attachments. The maximum number of attachments per message is: {maxAttachmentCount, number, integer}.","errorDuplicate":"This file is already attached."},"localOverride":false}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"pivot-table-and-date-sortinggrouped-by-monthsyears-when-empty-cells-in-selected-","messageId":"3257367","replyId":"3606371"},"buildId":"HEhyUrv5OXNBIbfCLaOrw","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"openTelemetryConfigName":"o365","openTelemetryServiceVersion":"25.1.0","openTelemetryUniverse":"prod","openTelemetryCollector":"http://localhost:4318","openTelemetryRouteChangeAllowedTime":"5000","apolloDevToolsEnabled":false,"inboxMuteWipFeatureEnabled":false},"isFallback":false,"isExperimentalCompile":false,"dynamicIds":["./components/seo/QAPageSchema/QAPageSchema.tsx","./components/community/Navbar/NavbarWidget.tsx","./components/community/Breadcrumb/BreadcrumbWidget.tsx","./components/customComponent/CustomComponent/CustomComponent.tsx","./components/messages/TopicWithThreadedReplyListWidget/TopicWithThreadedReplyListWidget.tsx","./components/external/components/ExternalComponent.tsx","./components/messages/MessageView/MessageViewStandard/MessageViewStandard.tsx","../shared/client/components/common/List/UnwrappedList/UnwrappedList.tsx","./components/tags/TagView/TagView.tsx","./components/tags/TagView/TagViewChip/TagViewChip.tsx","../shared/client/components/common/List/UnstyledList/UnstyledList.tsx","./components/messages/MessageView/MessageView.tsx","./components/attachments/AttachmentView/AttachmentView.tsx","./components/attachments/AttachmentView/AttachmentViewChip/AttachmentViewChip.tsx"],"appGip":true,"scriptLoader":[{"id":"analytics","src":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/pagescripts/1730819800000/analytics.js?page.id=ForumReplyPage&entity.id=board%3Aexcelgeneral&entity.id=message%3A3606371","strategy":"afterInteractive"}]}
