# Power Query Conditional Sort Based on Column Value



## cr731 (Apr 25, 2019)

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 (Apr 25, 2019)

maybe try *Order.Ascending* instead


----------



## cr731 (Apr 25, 2019)

sandy666 said:


> maybe try *Order.Ascending* instead



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


----------



## sandy666 (Apr 25, 2019)

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 (Apr 25, 2019)

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


----------



## sandy666 (Apr 25, 2019)

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 (Apr 25, 2019)

First option || Second option


*Type**Column1**Column2**Column3**Column4**Type**Column1**Column2**Column3**Column4*A9​AdamS7​B1​BrianA0​A7​JullietB8​B5​CelineA1​A5​FrankG8​A7​JullietB8​A9​NicoF8​A4​MarkC3​A8​IstvanU7​A4​ChesterC10​A4​ChesterC10​B1​ZillionD6​A2​EddieX8​B2​GoogleE1​A9​AlexZ6​A9​NicoF8​A4​MarkC3​A5​FrankG8​B2​GoogleE1​B6​OrpheusK9​B6​OrpheusK9​B6​HeroL3​B7​KrisY0​B8​LiviaQ4​B5​CelineA1​A9​AdamS7​B6​HeroL3​A8​IstvanU7​B1​BrianA0​B3​****W0​B8​LiviaQ4​A2​EddieX8​B1​ZillionD6​B7​KrisY0​B3​****W0​A9​AlexZ6​


----------



## billszysz (Apr 26, 2019)

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 (Apr 26, 2019)

billszysz said:


> Thanks  *sandy666 *for your table (from records) )



You are welcome 

Be my guest


----------

