Sorting each column individually in ascending order

[smpa01]

Hello experts,

Is it possbile to sort each column values indiaviaully in ascending orders?

For example, the source data is following

Column 1	Column 2	Column 3	Column 4	Column 5
1	2	3	4	5
2	1	1	1	1
3	3	2	3	4
4	4	4	5	2
5	5	5	2	3

Is it possible to achieve the following result by using M and not DAX

Column 1	Column 2	Column 3	Column 4	Column 5
1	1	1	1	1
2	2	2	2	2
3	3	3	3	3
4	4	4	4	4
5	5	5	5	5

Thank you in advance.

[Zubair_Muhammad]

@smpa01

One way could be to select individual columns as list >>then sort them and recombine them using Table.FromColumns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6VYnWiwiCEKBokaQ9Uh1INETaA6IbpBsiBRUyjPFK4+NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t]),
    CT = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Column 3", Int64.Type}, {"Column 4", Int64.Type}, {"Column 5", Int64.Type}}),
    Final=Table.FromColumns(
        {List.Sort(CT[Column 1],Order.Ascending),
         List.Sort(CT[Column 2],Order.Ascending),
         List.Sort(CT[Column 3],Order.Ascending),
         List.Sort(CT[Column 4],Order.Ascending),
         List.Sort(CT[Column 5],Order.Ascending)},
         
         {"Column 1","Column 2","Column 3","Column 4","Column 5"})
in
    Final

[Zubair_Muhammad · Accepted solution]

@smpa01

But if you have many columns (lets saymore than 50), a better approach would be to automate this using List.Generate
Please see attached file with both these ways

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6VYnWiwiCEKBokaQ9Uh1INETaA6IbpBsiBRUyjPFK4+NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t]),
    CT = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Column 3", Int64.Type}, {"Column 4", Int64.Type}, {"Column 5", Int64.Type}}),
    DH = Table.DemoteHeaders(CT),
    MyColumnNames=Record.ToList(DH{0}),
    Final=Table.FromColumns(
                    List.Generate(()=>
                    [x=0,y=Table.Column(CT,MyColumnNames{x})],
                    each [x] < List.Count(MyColumnNames),
                    each [x=[x]+1,y=Table.Column(CT,MyColumnNames{x})],
                    each List.Sort([y],Order.Ascending)),
            
            MyColumnNames)
    
    

    
in
    Final

[smpa01]

Thanks @Zubair_Muhammad for your help.