Forum Discussion
smpa01
Community Champion
Community ChampionSorting each column individually in ascending order
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.
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 wayslet 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
3 Replies
- Zubair_Muhammad
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
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 wayslet 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
smpa01Thanks Zubair_Muhammad for your help.