I'm trying to take a list of items, reorder the data in a single cell, and then alphabetize the column. Alphabetizing the column is easy but reordering the data in a cell is tripping me up. I know I ...
I think the positioning of a,c in the original is simply an error. Your implied challenge of replacing the letters with words would present a significant challenge because the sort would need a 2D array which is still a Microsoft fail. The steps should be:
Split comma-separated lists by row to 2D-array [MS fail]
Sort by row, returning a 2D-array [MS fail]
Sort array by columns {1,2,3} [OK]
Concatenate rows [OK]
That leaves Microsoft with a score of 2 out of 4; COULD DO BETTER!😛
This game of clicking 'show more' and typing links to other contributors is getting somewhat tedious. Your formula works perfectly. It took me a moment to realise that you were defining Lambda functions and using thunks in the formula.
I finished up with
Worksheet formula
= SORTLISTWORDSλ(original)
where
SORTLISTWORDSλ
= LAMBDA(list,
LET(
// Form overall list of sorted rows
array, MAPλ(list,
LAMBDA(string,
// Sort comma-separated strings
LET(
split, TRIM(TEXTSPLIT(string, ",")),
sorted, SORT(split, , 1, TRUE),
sorted
)
)
),
sortedList, SORT(array, {2,3, 4}),
orderNum, VALUE(TAKE(array,,1)),
BYROW(SORTBY(array, orderNum), ARRAYTOTEXT)
)
);
The complexity is hidden in the MAPλ function which is one of my standard workhorses that I use to work around the limitations of the built-in MAP function. One reason for the excessive length is that I have sorted the first column as numbers with 5 coming before 10.
I am aware that the OP asked for PQ, but you seem to have that covered. Who said that PQ is a low code environment?
PeterBartholomew1 , I had no doubt you do that in more elegant way. But for that we need to have your MAPλ in the library. As for PQ that's Lorenzo who gave more universal and advanced solution, if only to change slightly on working with words.
In general, working in PQ mainly with SQL database and OData sources I prefer set of low code simple steps. Since more complex coding quite often breaks query folding. But if we break it in any case, such coding is definitely the advantage.
Yes, MAPλ is a bit like a swan swimming: on the surface it looks serene, but that does not reflect what is going on below the waterline! I might be proud of it but, in reality, the sooner MS implements such functionality properly the better.
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\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/community/NavbarDropdownToggle\"]})":[{"__ref":"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/QueryHandler\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/QueryHandler-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/EscalatedMessageBanner\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/EscalatedMessageBanner-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/users/UserLink\"]})":[{"__ref":"CachedAsset:text:en_US-components/users/UserLink-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserRank\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserRank-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageTime\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageTime-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSolvedBadge\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSolvedBadge-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSubject\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSubject-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageBody\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageBody-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageCustomFields\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageCustomFields-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageReplyButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageReplyButton-1745505309751"}],"message({\"id\":\"message:4341852\"})":{"__ref":"ForumTopicMessage:message:4341852"},"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserAvatar\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/ranks/UserRankLabel\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/attachments/AttachmentView/AttachmentViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/AcceptedSolutionButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/ThreadedReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/ThreadedReplyList-1745505309751"}],"message({\"id\":\"message:4349286\"})":{"__ref":"ForumReplyMessage:message:4349286"},"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"components/tags/TagView/TagViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309751"}],"cachedText({\"lastModified\":\"1745505309751\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/Pager/PagerLoadMore\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/Pager/PagerLoadMore-1745505309751"}],"message({\"id\":\"message:4349986\"})":{"__ref":"ForumReplyMessage:message:4349986"},"message({\"id\":\"message:4350665\"})":{"__ref":"ForumReplyMessage:message:4350665"},"message({\"id\":\"message:4350819\"})":{"__ref":"ForumReplyMessage:message:4350819"}},"CachedAsset:pages-1745487429215":{"__typename":"CachedAsset","id":"pages-1745487429215","value":[{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"BlogViewAllPostsPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId/all-posts/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CasePortalPage","type":"CASE_PORTAL","urlPath":"/caseportal","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CreateGroupHubPage","type":"GROUP_HUB","urlPath":"/groups/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CaseViewPage","type":"CASE_DETAILS","urlPath":"/case/:caseId/:caseNumber","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"InboxPage","type":"COMMUNITY","urlPath":"/inbox","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"HelpFAQPage","type":"COMMUNITY","urlPath":"/help","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"IdeaMessagePage","type":"IDEA_POST","urlPath":"/idea/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"IdeaViewAllIdeasPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/all-ideas/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"LoginPage","type":"USER","urlPath":"/signin","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"BlogPostPage","type":"BLOG","urlPath":"/category/:categoryId/blogs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"UserBlogPermissions.Page","type":"COMMUNITY","urlPath":"/c/user-blog-permissions/page","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ThemeEditorPage","type":"COMMUNITY","urlPath":"/designer/themes","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TkbViewAllArticlesPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId/all-articles/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730142000000,"localOverride":null,"page":{"id":"AllEvents","type":"CUSTOM","urlPath":"/Events","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"OccasionEditPage","type":"EVENT","urlPath":"/event/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"OAuthAuthorizationAllowPage","type":"USER","urlPath":"/auth/authorize/allow","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"PageEditorPage","type":"COMMUNITY","urlPath":"/designer/pages","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"PostPage","type":"COMMUNITY","urlPath":"/category/:categoryId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForumBoardPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TkbBoardPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"EventPostPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"UserBadgesPage","type":"COMMUNITY","urlPath":"/users/:login/:userId/badges","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"GroupHubMembershipAction","type":"GROUP_HUB","urlPath":"/membership/join/:nodeId/:membershipType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"MaintenancePage","type":"COMMUNITY","urlPath":"/maintenance","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"IdeaReplyPage","type":"IDEA_REPLY","urlPath":"/idea/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"UserSettingsPage","type":"USER","urlPath":"/mysettings/:userSettingsTab","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"GroupHubsPage","type":"GROUP_HUB","urlPath":"/groups","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForumPostPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"OccasionRsvpActionPage","type":"OCCASION","urlPath":"/event/:boardId/:messageSubject/:messageId/rsvp/:responseType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"VerifyUserEmailPage","type":"USER","urlPath":"/verifyemail/:userId/:verifyEmailToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"AllOccasionsPage","type":"OCCASION","urlPath":"/category/:categoryId/events/:boardId/all-events/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"EventBoardPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TkbReplyPage","type":"TKB_REPLY","urlPath":"/kb/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"IdeaBoardPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CommunityGuideLinesPage","type":"COMMUNITY","urlPath":"/communityguidelines","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CaseCreatePage","type":"SALESFORCE_CASE_CREATION","urlPath":"/caseportal/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TkbEditPage","type":"TKB","urlPath":"/kb/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForgotPasswordPage","type":"USER","urlPath":"/forgotpassword","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"IdeaEditPage","type":"IDEA","urlPath":"/idea/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TagPage","type":"COMMUNITY","urlPath":"/tag/:tagName","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"BlogBoardPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"OccasionMessagePage","type":"OCCASION_TOPIC","urlPath":"/event/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ManageContentPage","type":"COMMUNITY","urlPath":"/managecontent","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ClosedMembershipNodeNonMembersPage","type":"GROUP_HUB","urlPath":"/closedgroup/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CommunityPage","type":"COMMUNITY","urlPath":"/","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForumMessagePage","type":"FORUM_TOPIC","urlPath":"/discussions/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"IdeaPostPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730142000000,"localOverride":null,"page":{"id":"CommunityHub.Page","type":"CUSTOM","urlPath":"/Directory","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"BlogMessagePage","type":"BLOG_ARTICLE","urlPath":"/blog/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"RegistrationPage","type":"USER","urlPath":"/register","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"EditGroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForumEditPage","type":"FORUM","urlPath":"/discussions/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ResetPasswordPage","type":"USER","urlPath":"/resetpassword/:userId/:resetPasswordToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730142000000,"localOverride":null,"page":{"id":"AllBlogs.Page","type":"CUSTOM","urlPath":"/blogs","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TkbMessagePage","type":"TKB_ARTICLE","urlPath":"/kb/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"BlogEditPage","type":"BLOG","urlPath":"/blog/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ManageUsersPage","type":"USER","urlPath":"/users/manage/:tab?/:manageUsersTab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForumReplyPage","type":"FORUM_REPLY","urlPath":"/discussions/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"PrivacyPolicyPage","type":"COMMUNITY","urlPath":"/privacypolicy","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"NotificationPage","type":"COMMUNITY","urlPath":"/notifications","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"UserPage","type":"USER","urlPath":"/users/:login/:userId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"OccasionReplyPage","type":"OCCASION_REPLY","urlPath":"/event/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ManageMembersPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/manage/:tab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"SearchResultsPage","type":"COMMUNITY","urlPath":"/search","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"BlogReplyPage","type":"BLOG_REPLY","urlPath":"/blog/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"GroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TermsOfServicePage","type":"COMMUNITY","urlPath":"/termsofservice","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"CategoryPage","type":"CATEGORY","urlPath":"/category/:categoryId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"ForumViewAllTopicsPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/all-topics/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"localOverride":null,"page":{"id":"TkbPostPage","type":"TKB","urlPath":"/category/:categoryId/kbs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429215,"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:36":{"__typename":"Rank","id":"rank:36","position":17,"name":"Brass Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"User:user:2426486":{"__typename":"User","id":"user:2426486","uid":2426486,"login":"renee_crozier","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/dS0yNDI2NDg2LTU3MjAyOGkxRTM1QjVCQjVCQTRFMzc0"},"rank":{"__ref":"Rank:rank:36"},"email":"","messagesCount":45,"biography":null,"topicsCount":18,"kudosReceivedCount":0,"kudosGivenCount":6,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2024-04-17T10:04:07.650-07:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0},"ForumTopicMessage:message:4341852":{"__typename":"ForumTopicMessage","uid":4341852,"subject":"Reorder Cell Data Using Power Query","id":"message:4341852","revisionNum":1,"repliesCount":22,"author":{"__ref":"User:user:2426486"},"depth":0,"hasGivenKudo":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4341852"},"readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4341852"},"body@stripHtml({\"truncateLength\":200})":" I'm trying to take a list of items, reorder the data in a single cell, and then alphabetize the column. Alphabetizing the column is easy but reordering the data in a cell is tripping me up. I know I ...","body@stringLength":"846","rawBody":"
I'm trying to take a list of items, reorder the data in a single cell, and then alphabetize the column. Alphabetizing the column is easy but reordering the data in a cell is tripping me up. I know I need to split by delimiter and recombine those cells but I'm unsure how to reorder.
Example of my data and what I'm looking to do:
Any help is much appreciated.
","kudosSumWeight":0,"postTime":"2024-11-27T08:57:01.363-08:00","images":{"__typename":"AssociatedImageConnection","edges":[{"__typename":"AssociatedImageEdge","cursor":"MjUuMXwyLjF8b3wyNXxfTlZffDE","node":{"__ref":"AssociatedImage:{\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00MzQxODUyLUNCbHMyNw?revision=1\"}"}}],"totalCount":1,"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":[{"__typename":"AttachmentEdge","cursor":"MjUuMXwyLjF8b3w1fF9OVl98MQ","node":{"__ref":"Attachment:{\"id\":\"attachment:message4341852AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/244437/1/Dummy%20Data.xlsx\"}"}}]},"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:4341852_1"},"latestVersion":null,"metrics":{"__typename":"MessageMetrics","views":324},"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:4341852":{"__typename":"Conversation","id":"conversation:4341852","solved":false,"topic":{"__ref":"ForumTopicMessage:message:4341852"},"lastPostingActivityTime":"2024-12-06T15:06:08.789-08:00","lastPostTime":"2024-12-06T15:06:08.789-08:00","unreadReplyCount":22,"isSubscribed":false},"ModerationData:moderation_data:4341852":{"__typename":"ModerationData","id":"moderation_data:4341852","status":"UNMODERATED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"AssociatedImage:{\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00MzQxODUyLUNCbHMyNw?revision=1\"}":{"__typename":"AssociatedImage","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00MzQxODUyLUNCbHMyNw?revision=1","title":"image.png","associationType":"BODY","width":236,"height":190,"altText":""},"Attachment:{\"id\":\"attachment:message4341852AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/244437/1/Dummy%20Data.xlsx\"}":{"__typename":"Attachment","id":"attachment:message4341852AttachmentNumber1","filename":"Dummy Data.xlsx","filesize":11059,"contentType":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/244437/1/Dummy%20Data.xlsx"},"Revision:revision:4341852_1":{"__typename":"Revision","id":"revision:4341852_1","lastEditTime":"2024-11-27T08:57:01.363-08:00"},"ForumReplyMessage:message:4349899":{"__typename":"ForumReplyMessage","id":"message:4349899","conversation":{"__ref":"Conversation:conversation:4341852"},"author":{"__ref":"User:user:521"},"revisionNum":1,"uid":4349899,"depth":6,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"subject":"Re: Reorder Cell Data Using Power Query","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4349899"},"parent":{"__ref":"ForumReplyMessage:message:4349286"},"body":"
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2024-11-27T11:19:55.376-08:00"},"ForumReplyMessage:message:4346550":{"__typename":"ForumReplyMessage","id":"message:4346550","revisionNum":1,"uid":4346550,"depth":3,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4341852"},"subject":"Re: Reorder Cell Data Using Power Query","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4346550"},"body":"
I am not convinced that the asked-for outcome doesn't itself contain an error. Anyway, studiously avoiding any temptation to go for brevity, how about
","author":{"__ref":"User:user:214174"},"isEscalated":null,"postTime":"2024-11-28T13:38:43.170-08:00","parent":{"__ref":"ForumReplyMessage:message:4342105"}},"ForumReplyMessage:message:4348054":{"__typename":"ForumReplyMessage","id":"message:4348054","revisionNum":1,"uid":4348054,"depth":4,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4341852"},"subject":"Re: Reorder Cell Data Using Power Query","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4348054"},"body":"
PeterBartholomew1 , I also don't know which exactly logic shall be behind. But if we change letter on word, e.g. \"c\" on \"com\", and assume expected outcome shall be the same, we have different results
\n","body@stringLength":"756","rawBody":"
, I also don't know which exactly logic shall be behind. But if we change letter on word, e.g. \"c\" on \"com\", and assume expected outcome shall be the same, we have different results
\n","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2024-11-28T23:21:41.368-08:00","parent":{"__ref":"ForumReplyMessage:message:4346550"}},"ForumReplyMessage:message:4349286":{"__typename":"ForumReplyMessage","id":"message:4349286","revisionNum":1,"uid":4349286,"depth":5,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4341852"},"subject":"Re: Reorder Cell Data Using Power Query","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4349286"},"body":"
I think the positioning of a,c in the original is simply an error. Your implied challenge of replacing the letters with words would present a significant challenge because the sort would need a 2D array which is still a Microsoft fail. The steps should be:
Split comma-separated lists by row to 2D-array [MS fail]
Sort by row, returning a 2D-array [MS fail]
Sort array by columns {1,2,3} [OK]
Concatenate rows [OK]
That leaves Microsoft with a score of 2 out of 4; COULD DO BETTER!😛
","body@stringLength":"549","rawBody":"
I think the positioning of a,c in the original is simply an error. Your implied challenge of replacing the letters with words would present a significant challenge because the sort would need a 2D array which is still a Microsoft fail. The steps should be:
Split comma-separated lists by row to 2D-array [MS fail]
Sort by row, returning a 2D-array [MS fail]
Sort array by columns {1,2,3} [OK]
Concatenate rows [OK]
That leaves Microsoft with a score of 2 out of 4; COULD DO BETTER!😛
","author":{"__ref":"User:user:214174"},"isEscalated":null,"postTime":"2024-11-29T01:58:57.418-08:00","parent":{"__ref":"ForumReplyMessage:message:4348054"},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"repliesCount":6},"Revision:revision:4349899_1":{"__typename":"Revision","id":"revision:4349899_1","lastEditTime":"2024-11-29T07:19:53.934-08:00"},"QueryVariables:ReplyList:message:4349899:1":{"__typename":"QueryVariables","id":"ReplyList:message:4349899:1","value":{"id":"message:4349899","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-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1745505309751","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1745505309751","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309751","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},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1745505309751","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-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-components/messages/ThreadedReplyList-1745505309751","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},"ModerationData:moderation_data:4349986":{"__typename":"ModerationData","id":"moderation_data:4349986","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4349986":{"__typename":"ForumReplyMessage","uid":4349986,"id":"message:4349986","revisionNum":1,"author":{"__ref":"User:user:214174"},"readOnly":false,"repliesCount":2,"depth":7,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4349899"},"conversation":{"__ref":"Conversation:conversation:4341852"},"subject":"Re: Reorder Cell Data Using Power Query","moderationData":{"__ref":"ModerationData:moderation_data:4349986"},"body":"
This game of clicking 'show more' and typing links to other contributors is getting somewhat tedious. Your formula works perfectly. It took me a moment to realise that you were defining Lambda functions and using thunks in the formula.
The complexity is hidden in the MAPλ function which is one of my standard workhorses that I use to work around the limitations of the built-in MAP function. One reason for the excessive length is that I have sorted the first column as numbers with 5 coming before 10.
I am aware that the OP asked for PQ, but you seem to have that covered. Who said that PQ is a low code environment?
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"218","kudosSumWeight":1,"postTime":"2024-11-29T11:51:17.964-08:00","lastPublishTime":"2024-11-29T11:51:17.964-08:00","metrics":{"__typename":"MessageMetrics","views":1},"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:4341852/message:4349986","replies":{"__typename":"MessageConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"MessageEdge","cursor":"MjUuMXwyLjF8aXwzfDEzMjowfGludCw0MzUwNjY1LDQzNTA2NjU","node":{"__ref":"ForumReplyMessage:message:4350665"}}]},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:4350665":{"__typename":"ModerationData","id":"moderation_data:4350665","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4350665":{"__typename":"ForumReplyMessage","uid":4350665,"id":"message:4350665","revisionNum":1,"author":{"__ref":"User:user:521"},"readOnly":false,"repliesCount":1,"depth":8,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4349986"},"conversation":{"__ref":"Conversation:conversation:4341852"},"subject":"Re: Reorder Cell Data Using Power Query","moderationData":{"__ref":"ModerationData:moderation_data:4350665"},"body":"
PeterBartholomew1 , I had no doubt you do that in more elegant way. But for that we need to have your MAPλ in the library. As for PQ that's Lorenzo who gave more universal and advanced solution, if only to change slightly on working with words.
\n
In general, working in PQ mainly with SQL database and OData sources I prefer set of low code simple steps. Since more complex coding quite often breaks query folding. But if we break it in any case, such coding is definitely the advantage.
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"203","kudosSumWeight":0,"postTime":"2024-11-30T01:44:59.308-08:00","lastPublishTime":"2024-11-30T01:44:59.308-08:00","metrics":{"__typename":"MessageMetrics","views":15},"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:4341852/message:4350665","replies":{"__typename":"MessageConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"MessageEdge","cursor":"MjUuMXwyLjF8aXwxfDEzMjowfGludCw0MzUwODE5LDQzNTA4MTk","node":{"__ref":"ForumReplyMessage:message:4350819"}}]},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:4350819":{"__typename":"ModerationData","id":"moderation_data:4350819","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4350819":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:214174"},"id":"message:4350819","revisionNum":1,"uid":4350819,"depth":9,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4350665"},"conversation":{"__ref":"Conversation:conversation:4341852"},"subject":"Re: Reorder Cell Data Using Power Query","moderationData":{"__ref":"ModerationData:moderation_data:4350819"},"body":"
Yes, MAPλ is a bit like a swan swimming: on the surface it looks serene, but that does not reflect what is going on below the waterline! I might be proud of it but, in reality, the sooner MS implements such functionality properly the better.
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"213","kudosSumWeight":0,"repliesCount":0,"postTime":"2024-11-30T15:52:51.019-08:00","lastPublishTime":"2024-11-30T15:52:51.019-08:00","metrics":{"__typename":"MessageMetrics","views":12},"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:4341852/message:4350819","customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1745505309751","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Pager/PagerLoadMore-1745505309751":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Pager/PagerLoadMore-1745505309751","value":{"loadMore":"Show More"},"localOverride":false}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"reorder-cell-data-using-power-query","messageId":"4341852","replyId":"4349899"},"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/attachments/AttachmentView/AttachmentView.tsx","./components/attachments/AttachmentView/AttachmentViewChip/AttachmentViewChip.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","../shared/client/components/common/Pager/PagerLoadMore/PagerLoadMore.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%3A4349899","strategy":"afterInteractive"}]}