Power Query Conditional Sort Based on Column Value

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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)

Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
you can play with this

Code:
[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
 
Upvote 0
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
 
Last edited:
Upvote 0
First option || Second option

TypeColumn1Column2Column3Column4TypeColumn1Column2Column3Column4
A
9​
AdamS
7​
B
1​
BrianA
0​
A
7​
JullietB
8​
B
5​
CelineA
1​
A
5​
FrankG
8​
A
7​
JullietB
8​
A
9​
NicoF
8​
A
4​
MarkC
3​
A
8​
IstvanU
7​
A
4​
ChesterC
10​
A
4​
ChesterC
10​
B
1​
ZillionD
6​
A
2​
EddieX
8​
B
2​
GoogleE
1​
A
9​
AlexZ
6​
A
9​
NicoF
8​
A
4​
MarkC
3​
A
5​
FrankG
8​
B
2​
GoogleE
1​
B
6​
OrpheusK
9​
B
6​
OrpheusK
9​
B
6​
HeroL
3​
B
7​
KrisY
0​
B
8​
LiviaQ
4​
B
5​
CelineA
1​
A
9​
AdamS
7​
B
6​
HeroL
3​
A
8​
IstvanU
7​
B
1​
BrianA
0​
B
3​
****W
0​
B
8​
LiviaQ
4​
A
2​
EddieX
8​
B
1​
ZillionD
6​
B
7​
KrisY
0​
B
3​
****W
0​
A
9​
AlexZ
6​
 
Upvote 0
Try this code
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) :))
 
Upvote 0
Thanks sandy666 for your table (from records) :))

You are welcome

Be my guest
rotfl2.gif
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top