Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Syndicate_Admin
Administrator
Administrator

How to Put 3 Columns of Numerical Data Into One and Ascending

Good

I would like to know with which dax code I can put the 3 columns together. That is, I want to create a new column where for each row I get together, ordering the numbers in ascending order. I want each number to go first to the smallest and so on in order to the largest.

Sebas98AM_0-1714862351749.png

For example:

It should be the new column:
11 18 31
14 17 39

But also a new board
Where the columns are like this

Num1 Num 2 Num3
11 18 31

That is, I created a new table with new columns and ordered each row numerically.

Thank you very much for your support

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Your data is not in a usable format. Add a row number column and then unpivot the other columns.  There is no need to pivot them back but you can add an index column if you want.

 

lbendlin_0-1714865528370.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZU0lEytAARhkqxOtFKhiYgtjmQMLZUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Num1 = _t, Num2 = _t, Num3 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Row", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Row"}, "Number", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Row", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Row"}, {{"Rows", each Table.AddIndexColumn(_, "Column", 1, 1, Int64.Type), type table [ Value=nullable Int64.Type, Column=Int64.Type]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Value", "Column"}, {"Value", "Column"})
in
    #"Expanded Rows"

 

 

 

You can then put that in a matrix visual

 

lbendlin_1-1714865694698.png

 

The code works for any number of columns, and no DAX is required.

 

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Your data is not in a usable format. Add a row number column and then unpivot the other columns.  There is no need to pivot them back but you can add an index column if you want.

 

lbendlin_0-1714865528370.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZU0lEytAARhkqxOtFKhiYgtjmQMLZUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Num1 = _t, Num2 = _t, Num3 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Row", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Row"}, "Number", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Row", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Row"}, {{"Rows", each Table.AddIndexColumn(_, "Column", 1, 1, Int64.Type), type table [ Value=nullable Int64.Type, Column=Int64.Type]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Value", "Column"}, {"Value", "Column"})
in
    #"Expanded Rows"

 

 

 

You can then put that in a matrix visual

 

lbendlin_1-1714865694698.png

 

The code works for any number of columns, and no DAX is required.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.