(Power Query) Complicated sort

Question

I have a complicated sorting that I want, and I'm just not sure how to get power query to do it. The TLDR version is "oldest new ones first, then newest old ones." So I want to split the sort between ascending/descending depending on what data are in the columns.

Certain columns on my sheet (I through K) contain the word 'Yes' if it is a new item, otherwise blank. Possible combinations of columns that have 'yes' in them:

I only, J only, K only, I + J, J + K, I + J + K

Here's the sort logic I want:

All rows with a Yes in K are listed first, ascending by date (column H), whether they have 'Yes' in columns I or J or not.

Then, Of only the rows that are left, all rows with a Yes in J, ascending by date (column H)

Next, Of only the rows that are left, all rows with a Yes in I, ascending by date (column H)

Finally, the only rows left should not have a Yes in any columns I-K. Of those rows, DEscending by date (Column H).

I can sort of maybe figure out how to do the sort up through step 3 by creating a custom column to label and identifying whether the row will go in the first, second, or third sort, then sorting by that custom column before sorting the others.

But step 4 is stumping me because of the reverse to descending instead of ascending. I'm thinking maybe grouping the data, sorting it within the group descending and outside the group ascending (as a 4th entry in the custom column that sorted the first 3), and then expanding it back out again after the external sort, or something?

Please help!

Currently I'm only able to sort the sheet ascending and can't sort part of it descending.

Answer 1

Filter a column, then sort it. Filter another column and sort it. etc. Put them together

Load your data into powerquery (data ... from table/range ... )and use code below pasted into home ... advanced editor.... It assumes your data is loaded as Table1 with column headers A,H,I,J,K, so change that to reflect your actual table name and column names. If you have your own code, remove the first row and change the Source in the second row to reflect your #"PriorStepName"

sample code to transform image below on left to image on right:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"H", type date}, {"I", type text}, {"J", type text}, {"K", type text}}),
Part1 = Table.Sort(Table.SelectRows(#"Changed Type", each ([K] = "Yes")),{{"H", Order.Ascending}}),
Part2 = Table.Sort(Table.SelectRows(#"Changed Type", each ([K] <> "Yes" and [J] = "Yes")),{{"H", Order.Ascending}}),
Part3 = Table.Sort(Table.SelectRows(#"Changed Type", each ([K] <> "Yes" and [J] <> "Yes" and [I] = "Yes")),{{"H", Order.Ascending}}),
Part4 = Table.Sort(Table.SelectRows(#"Changed Type", each ([K] <> "Yes" and [J] <> "Yes" and [I] <> "Yes")),{{"H", Order.Descending}}),
Combined = Table.Combine({Part1,Part2,Part3,Part4})
in Combined