Power Query Conditional Sort Based on Column Value

[cr731]

I have a table containing a column called "Type" and I want to sort the rows of the table differently based on the value in that column. For example,

if Type = "A" then sort by {{"Column1", Sort.Ascending}, {"Column2", Sort.Ascending}}
if Type = "B" then sort by {{"Column3", Sort.Ascending}, {"Column4", Sort.Ascending}}

Is this possible to do? I tried this (assuming in this example that possible values in Type are only A and B)

Table.Sort(MyTable, each if [Type] = "A" then {{"Column1", Sort.Ascending}, {"Column2", Sort.Ascending}} else {{"Column3", Sort.Ascending}, {"Column4", Sort.Ascending}})

This gives me an error: Expression.Error: We cannot apply operator < to types List and List.

My alternative was to split the table into two based on Type, sort those tables, then append. That feels rather messy if it's possible to do this in one step using Table.Sort but I am not sure if it's possible.

Thanks

 

[sandy666]

maybe try Order.Ascending instead

 

[cr731]

maybe try Order.Ascending instead

Sorry, that was just a typo in my example. My actual query did use Order.Ascending.

 

[sandy666]

you can play with this

[SIZE=1]let
    Source = Table.Sort(
        Table.FromRecords(
            {
            [Type =	"A",	Column1 =9,	Column2 = "Alex",	Column3 = "Z",	Column4 = 6],
            [Type =	"B",	Column1 =5,	Column2 = "Celine",	Column3 = "A",	Column4 = 1],
            [Type =	"A",	Column1 =4,	Column2 = "Mark",	Column3 = "C",	Column4 = 3],
            [Type =	"B",	Column1 =3,	Column2 = "****",	Column3 = "W",	Column4 = 0],
            [Type =	"A",	Column1 =2,	Column2 = "Eddie",	Column3 = "X",	Column4 = 8],
            [Type =	"B",	Column1 =1,	Column2 = "Brian",	Column3 = "A",	Column4 = 0],
            [Type =	"A",	Column1 =5,	Column2 = "Frank",	Column3 = "G",	Column4 = 8],
            [Type =	"B",	Column1 =6,	Column2 = "Hero",	Column3 = "L",	Column4 = 3],
            [Type =	"A",	Column1 =7,	Column2 = "Julliet",	Column3 = "B",	Column4 = 8],
            [Type =	"B",	Column1 =8,	Column2 = "Livia",	Column3 = "Q",	Column4 = 4],
            [Type =	"A",	Column1 =9,	Column2 = "Adam",	Column3 = "S",	Column4 = 7],
            [Type =	"B",	Column1 =1,	Column2 = "Zillion",	Column3 = "D",	Column4 = 6],
            [Type =	"A",	Column1 =4,	Column2 = "Chester",	Column3 = "C",	Column4 = 10],
            [Type =	"B",	Column1 =2,	Column2 = "Google",	Column3 = "E",	Column4 = 1],
            [Type =	"A",	Column1 =8,	Column2 = "Istvan",	Column3 = "U",	Column4 = 7],
            [Type =	"B",	Column1 =7,	Column2 = "Kris",	Column3 = "Y",	Column4 = 0],
            [Type =	"A",	Column1 =9,	Column2 = "Nico",	Column3 = "F",	Column4 = 8],
            [Type =	"B",	Column1 =6,	Column2 = "Orpheus",	Column3 = "K",	Column4 = 9]
            }
                ),

/* comment this line, release next line */
        {{"Type", Order.Ascending}})

/*comment this line, release previous line */
//        if "Type" ="A" then {{"Column1", Order.Ascending},{"Column2", Order.Ascending}} else {{"Column3", Order.Ascending},{"Column4", Order.Ascending}})

in
    Source[/SIZE]

and try with table as source

I do not have time right now

 

[cr731]

Maybe I'm missing something, but doesn't that just sort ascending by Type?

 

[sandy666]

look at first option (under first comment) and compare it to order of type in records
or just change to Order.Descending

play with comment/uncomment options but not both at once and you'll see

 

[sandy666]

First option || Second option

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][td=bgcolor:#70AD47]Column4[/td][td][/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][td=bgcolor:#70AD47]Column4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

9​

[/td][td=bgcolor:#E2EFDA]Adam[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]

7​

[/td][td][/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Brian[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]

7​

[/td][td]Julliet[/td][td]B[/td][td]

8​

[/td][td][/td][td]B[/td][td]

5​

[/td][td]Celine[/td][td]A[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]Frank[/td][td=bgcolor:#E2EFDA]G[/td][td=bgcolor:#E2EFDA]

8​

[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

7​

[/td][td=bgcolor:#E2EFDA]Julliet[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]

9​

[/td][td]Nico[/td][td]F[/td][td]

8​

[/td][td][/td][td]A[/td][td]

4​

[/td][td]Mark[/td][td]C[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

8​

[/td][td=bgcolor:#E2EFDA]Istvan[/td][td=bgcolor:#E2EFDA]U[/td][td=bgcolor:#E2EFDA]

7​

[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]Chester[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]

4​

[/td][td]Chester[/td][td]C[/td][td]

10​

[/td][td][/td][td]B[/td][td]

1​

[/td][td]Zillion[/td][td]D[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Eddie[/td][td=bgcolor:#E2EFDA]X[/td][td=bgcolor:#E2EFDA]

8​

[/td][td][/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Google[/td][td=bgcolor:#E2EFDA]E[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]

9​

[/td][td]Alex[/td][td]Z[/td][td]

6​

[/td][td][/td][td]A[/td][td]

9​

[/td][td]Nico[/td][td]F[/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]Mark[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]

3​

[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]Frank[/td][td=bgcolor:#E2EFDA]G[/td][td=bgcolor:#E2EFDA]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

2​

[/td][td]Google[/td][td]E[/td][td]

1​

[/td][td][/td][td]B[/td][td]

6​

[/td][td]Orpheus[/td][td]K[/td][td]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]Orpheus[/td][td=bgcolor:#E2EFDA]K[/td][td=bgcolor:#E2EFDA]

9​

[/td][td][/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]Hero[/td][td=bgcolor:#E2EFDA]L[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

7​

[/td][td]Kris[/td][td]Y[/td][td]

0​

[/td][td][/td][td]B[/td][td]

8​

[/td][td]Livia[/td][td]Q[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]Celine[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

9​

[/td][td=bgcolor:#E2EFDA]Adam[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]

7​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

6​

[/td][td]Hero[/td][td]L[/td][td]

3​

[/td][td][/td][td]A[/td][td]

8​

[/td][td]Istvan[/td][td]U[/td][td]

7​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Brian[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

0​

[/td][td][/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]****[/td][td=bgcolor:#E2EFDA]W[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

8​

[/td][td]Livia[/td][td]Q[/td][td]

4​

[/td][td][/td][td]A[/td][td]

2​

[/td][td]Eddie[/td][td]X[/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Zillion[/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]

6​

[/td][td][/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

7​

[/td][td=bgcolor:#E2EFDA]Kris[/td][td=bgcolor:#E2EFDA]Y[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

3​

[/td][td]****[/td][td]W[/td][td]

0​

[/td][td][/td][td]A[/td][td]

9​

[/td][td]Alex[/td][td]Z[/td][td]

6​

[/td][/tr]
[/table]

 

[billszysz]

Try this code

let

    Col1 = {"Column1", Order.Ascending},
    Col2 = {"Column2", Order.Ascending},
    Col3 = {"Column3", Order.Ascending},
    Col4 = {"Column4", Order.Ascending},
    SortA = {Col1, Col2},
    SortB = {Col3, Col4},


    Source = Table.FromRecords(
            {
            [Type =	"A",	Column1 =9,	Column2 = "Alex",	Column3 = "Z",	Column4 = 6],
            [Type =	"B",	Column1 =5,	Column2 = "Celine",	Column3 = "A",	Column4 = 1],
            [Type =	"A",	Column1 =4,	Column2 = "Mark",	Column3 = "C",	Column4 = 3],
            [Type =	"B",	Column1 =3,	Column2 = "****",	Column3 = "W",	Column4 = 0],
            [Type =	"A",	Column1 =2,	Column2 = "Eddie",	Column3 = "X",	Column4 = 8],
            [Type =	"B",	Column1 =1,	Column2 = "Brian",	Column3 = "A",	Column4 = 0],
            [Type =	"A",	Column1 =5,	Column2 = "Frank",	Column3 = "G",	Column4 = 8],
            [Type =	"B",	Column1 =6,	Column2 = "Hero",	Column3 = "L",	Column4 = 3],
            [Type =	"A",	Column1 =7,	Column2 = "Julliet",	Column3 = "B",	Column4 = 8],
            [Type =	"B",	Column1 =8,	Column2 = "Livia",	Column3 = "Q",	Column4 = 4],
            [Type =	"A",	Column1 =9,	Column2 = "Adam",	Column3 = "S",	Column4 = 7],
            [Type =	"B",	Column1 =1,	Column2 = "Zillion",	Column3 = "D",	Column4 = 6],
            [Type =	"A",	Column1 =4,	Column2 = "Chester",	Column3 = "C",	Column4 = 10],
            [Type =	"B",	Column1 =2,	Column2 = "Google",	Column3 = "E",	Column4 = 1],
            [Type =	"A",	Column1 =8,	Column2 = "Istvan",	Column3 = "U",	Column4 = 7],
            [Type =	"B",	Column1 =7,	Column2 = "Kris",	Column3 = "Y",	Column4 = 0],
            [Type =	"A",	Column1 =9,	Column2 = "Nico",	Column3 = "F",	Column4 = 8],
            [Type =	"B",	Column1 =6,	Column2 = "Orpheus",	Column3 = "K",	Column4 = 9]
            }
                ),
    #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"tbl", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sorted", each if [Type] = "A" then Table.Sort([tbl], SortA) else Table.Sort([tbl], SortB)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Sorted"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "Sorted", {"Type", "Column1", "Column2", "Column3", "Column4"}, {"Type", "Column1", "Column2", "Column3", "Column4"})
in
    #"Expanded {0}"

Thanks sandy666 for your table (from records) )

 

[sandy666]

Thanks sandy666 for your table (from records) )

You are welcome

Be my guest