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}})
You can add a new Order column to the table using the following expression:
= Table.AddColumn(#"Changed Type", "Order", each if [Rating] = "High" then "1" else if [Rating] = "Medium" then "2" else "3" )
Then you just need to sort ascending the Order column.
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])))
