Jul 30 2020 09:12 AM
I need help getting the after sort results in my photo.
Jul 30 2020 09:17 AM
Aug 03 2020 10:43 PM
@jerbear In your example you have a range of 10 rows and 4 columns containing 35 names an 5 blanks. Your aim is to sort these in alphabetical order and display them side-by-side in groups of max 10.
I suspect that your real situation isn't as straightforward, but am offering you a power query (PQ) solution that does exactly what you ask for in the example. I am fully aware that this is not the most elegant or dynamic solution. A real expert in PQ can probably come-up with a better way.
Aug 04 2020 01:56 AM - edited Aug 04 2020 01:58 AM
A formula solution, provided you have the correct version of Excel!
= LET(
unpivot, LET(
k, SEQUENCE(40,1,0),
rowNum, MOD(k,10),
colNum, QUOTIENT(k,10),
INDEX(Table1,1+rowNum,1+colNum) ),
sort, LET(
sorted, SORT(unpivot),
IF(sorted<>0, sorted, "") ),
repivot, LET(
idxArray, SEQUENCE(10,1,0)+10*SEQUENCE(1,4,0),
INDEX(sort, 1+idxArray) ),
repivot )
I refactored the formula because it was you that first demonstrated the nested LET to me!
Aug 04 2020 02:04 AM
Would adding an index column and unpivoting other columns involve fewer queries?
Aug 04 2020 02:44 AM - edited Aug 04 2020 02:57 AM
Aug 04 2020 02:52 AM
The idea was in the air, if I found that an hour earlier than you that means nothing.
Even if LET() is on Current (Preview) now I still avoid to use it in MTC discussion, will wait availability at least on Current.
Aug 04 2020 03:52 AM
A small solution would be a VBA solution like the one in the attached file. Is only an additional suggestion :)
Sub Sort() Dim strSpalte As String Dim strBereich As String 'Parameter strBereich = "A1:B11" 'Area strSpalte = "A" 'column 'Sort With Tabelle1 'Sheet Range(strBereich).Sort _ Key1:=Range(strSpalte & "1"), Order1:=xlAscending, _ Header:=xlYes, MatchCase:=True End With End Sub
If you liked one of the suggested solutions, please click on it as the correct answer, if possible with a like (thumbs up). If none is the solution you are looking for, please give us a quick feedback.
Best regards,
Nikolino
I know I don't know anything (Socrates)
Aug 04 2020 05:55 AM
To play with coding
let
Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
SortedSource = Table.FromColumns(
List.Split(
List.Sort(
List.Distinct(
List.RemoveNulls(
List.Union(
Table.ToColumns(Source)
)
)
)
),
Table.RowCount(Source)
),
Table.ColumnNames(Source)
)
in
SortedSource
Aug 04 2020 11:52 AM
"Even if LET() is on Current (Preview) now I still avoid to use it in MTC discussion"
I was going to say that was because you are more responsible than I, and simply seek to help, whereas I look for challenges that allow me to develop the techniques I can apply to a range of problems.
With your nested PQ solution, I am not so sure! Whereas, with the LET function, I introduced local names to provide documentation and remove nesting, you have travelled a similar road in the opposite direction, albeit with a striking indentation pattern. I expect @Riny_van_Eekelen coped just fine, but it took me a while before I settled on reading from the deepest nesting outwards and I also had to come to terms with some list methods that I would not be able to access from the GUI!
I still have the feeling that I am not going to become fluent with such a style anytime soon!
Thanks anyway.
Aug 04 2020 12:57 PM
@Peter Bartholomew , if I do this query from scratch I'd do it approximately the same way as @Riny_van_Eekelen did. Reasons - more UI is better for shared solutions; UI is usually better from maintenance point of view. Plus from performance point of view Power Query is better optimized to work with tables rather than with lists. However, performance is half a technology, another half is the art. Approach depends on concrete case.
Nested list functions are only to demonstrate alternatives. I fully agree, from usability point of view same code organised by steps is much better
let
Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
combineColumnsInOneList = List.Union(Table.ToColumns(Source)),
removeNullsFromList = List.RemoveNulls(combineColumnsInOneList),
removeDuplicates = List.Distinct(removeNullsFromList),
sortList = List.Sort(removeDuplicates),
rowsInTable = Table.RowCount(Source),
splitListOnFutureColumns= List.Split(sortList,rowsInTable),
generateSortedTable = Table.FromColumns(
splitListOnFutureColumns,
Table.ColumnNames(Source)
)
in
generateSortedTable
Nested variant is bit better only from development point of view, at least for me.
Aug 04 2020 02:28 PM
The is nothing special about the result; the point is that this was the first time I had ever put together M code using the advanced editor!
let
Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
SortedList = List.Sort(
List.Distinct(
List.RemoveNulls(
List.Union(
Table.ToColumns(Source)
)
)
)
),
n = 1+Number.IntegerDivide(List.Count(SortedList),4),
LoadToTable = Table.FromColumns(
List.Split(SortedList,n),
Table.ColumnNames(Source)
)
in
LoadToTable
Thank you for the model code and guidance.
Aug 04 2020 02:57 PM
The only what is discussable here is
n = 1+Number.IntegerDivide(List.Count(SortedList),4)
why the magic 4 appeared. But that's not clear from the specification - shall resulting table be the same size as the source, or or only same number of columns, or some number of rows and doesn't matter how many columns. If 4 is predefined when no problems, otherwise it shall be calculatable.
Aug 04 2020 03:19 PM
True. The 4 should have been
Table.Column.Count(Source)
I wanted to change the functionality, just for the same of doing something different as an exercise. The useful side effect is that the process copes better with the situation in which more than a quarter of the cells are null, which potentially reduces the output to 3 columns, depending on the algorithm.
I decided to maintain the number of columns but reduce the records. I do not know what the OP would have wanted, but I suspect he has long since left the discussion.