Forum Discussion
Reorder Cell Data Using Power Query
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) ) ) )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.
I agree, I couldn't make sense of the sort order. I can get there with a LAMBDA function, but the weird sort order throws me. I can't understand the logic of the sort order for expected outcomes
Mark_J_Walker , I also don't know which exactly logic shall be. My understandings:
- we sort words in each row of the original list in alphabetical order
- sort list by first word in each row, next by number of words in the rest of each row, next for each group with the same number of words in alphabetical order
