Log In
Register
Home
January 20, 2019
Offline
I have the following table that I filtered in Power Query on the column “Jockey”.
| DATE | RACE | HORSE | HORSE NO | JOCKEY | ML |
| 11/29/2019 | 4 | Rio Vista | 7 | Angel Castillo | 6/1 |
| 11/29/2019 | 3 | My Man Elvis | 4 | Angel Castillo | 5/2 |
| 11/29/2019 | 1 | Valiant Spirit | 4 | Angel Castillo | 10/1 |
| 11/29/2019 | 5 | Ring Necked | 8 | Angel Castillo | 8/1 |
| 11/29/2019 | 8 | Shanghai Superfly | 1 | Frankie Pennington | 3/2 |
| 11/29/2019 | 7 | She's Primo | 7 | Frankie Pennington | 5/1 |
| 11/29/2019 | 8 | Zoomer | 1A | Frankie Pennington | 3/2 |
| 11/29/2019 | 6 | Not in the Mood | 5 | Jann N. Hernandez | 7/2 |
| 11/29/2019 | 1 | Lemon Crush | 1 | Jann N. Hernandez | 4/1 |
| 11/29/2019 | 2 | Spider Jorgensen | 4 | Jann N. Hernandez | 3/1 |
| 11/29/2019 | 3 | Vorian | 5 | John Bisono | 9/2 |
| 11/29/2019 | 8 | He's Incredible | 3 | John Bisono | 4/1 |
| 11/29/2019 | 5 | Banker's Island | 10 | John Bisono | 10/1 |
| 11/29/2019 | 7 | Di | 1 | John Bisono | 2/1 |
| 11/29/2019 | 8 | Bojingle | 5 | Mychel J. Sanchez | 3/1 |
| 11/29/2019 | 1 | Creative Princess | 6 | Mychel J. Sanchez | 8/1 |
| 11/29/2019 | 7 | Dance and Dance | 6 | Mychel J. Sanchez | 7/2 |
| 11/29/2019 | 4 | Imperial Lady | 2 | Mychel J. Sanchez | 8/1 |
I also need to filter the table by column “Race” in race number order (ascending) keeping the “Jockey” order so it looks as below.
| DATE | RACE | HORSE | HORSE NO | JOCKEY | ML |
| 11/29/2019 | 1 | Valiant Spirit | 4 | Angel Castillo | 10/1 |
| 11/29/2019 | 3 | My Man Elvis | 4 | Angel Castillo | 5/2 |
| 11/29/2019 | 4 | Rio Vista | 7 | Angel Castillo | 6/1 |
| 11/29/2019 | 5 | Ring Necked | 8 | Angel Castillo | 8/1 |
| 11/29/2019 | 7 | She's Primo | 7 | Frankie Pennington | 5/1 |
| 11/29/2019 | 8 | Shanghai Superfly | 1 | Frankie Pennington | 3/2 |
| 11/29/2019 | 8 | Zoomer | 1A | Frankie Pennington | 3/2 |
| 11/29/2019 | 1 | Lemon Crush | 1 | Jann N. Hernandez | 4/1 |
| 11/29/2019 | 2 | Spider Jorgensen | 4 | Jann N. Hernandez | 3/1 |
| 11/29/2019 | 6 | Not in the Mood | 5 | Jann N. Hernandez | 7/2 |
| 11/29/2019 | 3 | Vorian | 5 | John Bisono | 8/1 |
| 11/29/2019 | 5 | Banker's Island | 10 | John Bisono | 7/2 |
| 11/29/2019 | 7 | Di | 1 | John Bisono | 3/1 |
| 11/29/2019 | 8 | He's Incredible | 3 | John Bisono | 8/1 |
| 11/29/2019 | 1 | Creative Princess | 6 | Mychel J. Sanchez | 8/1 |
| 11/29/2019 | 4 | Imperial Lady | 2 | Mychel J. Sanchez | 8/1 |
| 11/29/2019 | 7 | Dance and Dance | 6 | Mychel J. Sanchez | 7/2 |
| 11/29/2019 | 8 | Bojingle | 5 | Mychel J. Sanchez | 3/1 |
Can this be done in Power Query? I searched around the internet, but I could not find where you could filter “Race” while keeping “Jockey” in order. I didn’t get a chance to dig into Mynda’s last video to see if that would apply. If it did, let me know and I’ll work on it.
Code to select Jockeys:
= Table.SelectRows(#"Renamed Columns", each ([JOCKEY] = " Angel Castillo" or [JOCKEY] = " Frankie Pennington" or [JOCKEY] = " Jann N. Hernandez" or [JOCKEY] = " John Bisono" or [JOCKEY] = " Mychel J. Sanchez"))
Code to sort Jockeys:
= Table.Sort(#"Filtered Rows",{{"JOCKEY", Order.Ascending}})
See Answer
July 16, 2010
Offline
Hi Cedric,
Sort the jockey column, then click on the race column and sort it. That will perform a 2 column sort. You should see a small 1 in the jockey column header and a small 2 in the race column header.
Mynda
Answers Post
November 8, 2013
Offline
Or, you can manually edit the formula:
= Table.Sort(#"Filtered Rows",{{"JOCKEY", Order.Ascending}}) will be:
= Table.Sort(#"Filtered Rows",{{"JOCKEY", Order.Ascending},{"Race", Order.Ascending}})
January 20, 2019
Offline
Hi Mynda and Catalin,
Thank you both for taking time to reply. Both are perfect answers and both of you are the greatest.
I knew there had to be a simple answer to my question because Power Query is such a powerful program.
Wishing both of you the best for the upcoming holiday season, and Live Long and Prosper.
Cedric
1 Guest(s)
