Sort cells within the same row

[danielrsnyder]

Hello wise ones. I have a dataset that contains child class years and I want them to be in order, oldest to greatest. Ex. in the first row it should be 1982, 1992, 1994. Any idea of a way to do that in Power Query?

[v-yalanwu-msft · Accepted solution]

Hi, @danielrsnyder ;

You also could try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0NFHSAVIWRmDKEkTllebkKMXqgGQtLBDCRgYGhmiyUE0GECMMwIoMzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child Class of" = _t, #"Child Class of2" = _t, #"Child Class of3" = _t, #"Child Class of4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child Class of", Int64.Type}, {"Child Class of2", Int64.Type}, {"Child Class of3", Int64.Type}, {"Child Class of4", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Group", 1, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Group"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Group"}, {{"Sort", each Table.AddIndexColumn( Table.Sort(_,{{"Group", Order.Ascending}, {"Value", Order.Ascending}}), "sort",1,1), type table}}),
    #"Expanded Sort" = Table.ExpandTableColumn(#"Grouped Rows", "Sort", {"Attribute", "Value", "sort"}, {"Attribute", "Value", "sort.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Sort",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "Child Class of"& Text.From([sort.1])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"sort.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Value", List.Sum),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Group"})
in
    #"Removed Columns2"

Thr orginal table.

and the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danielrsnyder]

@v-yalanwu-msft Thanks for this, it worked great for my needs and got me to the solution I needed.

[ronrsnfld]

One of the problems with an Ascending Sort is that it will move the nulls to the start.  To deal with that issue, I sort each row ascending; Remove the nulls; then pad the end of the list with nulls so each row will have the correct number of entries:

let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child Class of", Int64.Type}, {"Child Class of2", Int64.Type}, {"Child Class of3", Int64.Type}, {"Child Class of4", Int64.Type}}),

        ColCount=Table.ColumnCount(#"Changed Type"),
        colHdrs = Table.ColumnNames(#"Changed Type"),

//With Ascending sort, nulls will be at the start
//  Note the padding to move them to the end
    #"Sorted Rows" = 
        Table.FromRows(
            List.Accumulate({0..Table.RowCount(#"Changed Type")-1}, {}, (state, current)=> state & 
                {List.Split(
                    List.Sort(
                        List.RemoveNulls(
                            Record.FieldValues(#"Changed Type"{current})), Order.Ascending) & 
                                List.Repeat({null}, ColCount), 
                    ColCount){0}}), 
            colHdrs),

    Typed = Table.TransformColumnTypes(#"Sorted Rows", List.Transform(colHdrs, each {_, Int64.Type}))
in
    Typed

[lbendlin]

This points to a bad source data format design.  What are you actually trying to accomplish?