Search code examples
excelpowerpivotpowerquery

Sorting in a specfic Order using PowerQuery

I have a table with 5 fields with a column containing the following:

Medium High Low

If I sort in ascending order the data sorts in alphabetical order:

High Low Medium

In the advanced editor how do I change the code in PowerQuery so that it sorts like the below:

High Medium Low

My current syntax in the editor is this:

Table.Sort(#"Filtered Rows",{{"Rating", Order.Ascending}})

Solution

  • Alternatively, you can make use of the full strength of the comparisonCriteria argument of Table.Sort:

    = Table.Sort(#"Filtered Rows", (x,y) => Value.Compare(List.PositionOf({"Low","Medium","High"},x[Rating]),List.PositionOf({"Low","Medium","High"},y[Rating])))

    Edit: this is like Order.Ascending (Low, Medium, High), but - reading your question again - I see you are looking for High, Medium, Low: so you can switch "Low" and "High" in the code (2x).

    Edit2: another cool trick is to leave the original sequence and multiply the result from Value.Compare by -1 to reverse the sort order:

    = Table.Sort(#"Filtered Rows", (x,y) => -1 * Value.Compare(List.PositionOf({"Low","Medium","High"},x[Rating]),List.PositionOf({"Low","Medium","High"},y[Rating])))