Forum Discussion
Reorder Cell Data Using Power Query
HansVogelaar , that's not exactly asked order
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
= SORTLISTITEMSλ(original)
SORTLISTITEMSλ
= LAMBDA(list,
// Sort overall list alphabetically
SORT(
MAP(list,
LAMBDA(string,
// Sort comma-separated strings and reassemble
LET(
split, TRIM(TEXTSPLIT(string, ",")),
sorted, SORT(split, , 1, TRUE),
ARRAYTOTEXT(sorted)
)
)
)
)
);
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
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!😛
PeterBartholomew1 , something like this works
=LET( list, A2:A10, split, LAMBDA(v, LAMBDA(SORT( TEXTSPLIT(v,", "), , ,1 ) ) ), levels, MAP(list, LAMBDA(v, COLUMNS(split(v)() ) ) ), first, MAP(list, LAMBDA(v, @split(v)() ) ), second, MAP(list, LAMBDA(v, IFERROR( TEXTJOIN(", ", ,(DROP(split(v)(),,1) ) ), "" ) ) ), BYROW( DROP( SORT( HSTACK( first, second, levels ), {1,3} ), , -1), LAMBDA(v, TEXTJOIN(", ", , v) ) ) )