Forum Discussion
Sort Rows by Attributes
Hello - I was wondering if there was a to sort data based on your preferred order. I currently have everything sorting in Ascending order, but would like to change the "Clearance" column to sort by the values in this order (Morning, Day, Ext. Day, Evening or Night, 24Hour) Some locations use Evening and some use Night but they should be treated the same for the sorting order.
= Table.Sort(#"Removed Errors",{{"Location", Order.Ascending},{"Building", Order.Ascending},{"Employee Name", Order.Ascending},{"Clearance", Order.Ascending}})
7 Replies
Hi Anonymous ,
You'll need to add a conditional column for a sort key to sort by, something like this:
if [Clearance] = "Morning" then 10 else if [Clearance] = "Day" then 20 else if [Clearance] = "Ext. Day" then 30 ... ... else 9999Pete
BA_Pete I tried adding that before but it didn't change the order of the clearance type, it was still sorting by alpha order. I tried sorting based on another column but then it messes up the custom sort that I already have in place. Below is what I have now:
= Table.Sort(#"Removed Errors",{{"Location", Order.Ascending},{"Building", Order.Ascending},{"Employee Name", Order.Ascending},{"Clearance Sort Order", Order.Ascending}})
Hello there,
Based on the formula you sent above, it is sorting first Location, then Building, then Employee Name and then Clearance Sort Order last. As a result of sorting Clearance Sort Order last, you will see the data as being sortee by Clearance despite the corrections you made.
I would change the formula to this
= Table.Sort(#"Removed Errors",{{"Name of Column", Order.Ascending}})
By Name of Column - I would use the column you created using Pete's formula above. Let's Say the name of the column was "Classification", then the formula becomes
= Table.Sort(#"Removed Errors",{{"Classification", Order.Ascending}})
Hope that helps and do let me know if it works!
Apologies I didn't realise you needed those in the sort as well - thought it was just by the Clearance one from the description. In that case, I can't see why there would be any issue with what you first did, i.e.
= Table.Sort(#"Removed Errors",{{"Location", Order.Ascending},{"Building", Order.Ascending},{"Employee Name", Order.Ascending},{"Clearance Sort Order", Order.Ascending}}))
My supicion is that you may have made a mistake in the formula that Pete suggested, as that's the only place I can see an issue. Eitherways, in case you can't find a solution with that I might suggest another approach in that you could load the data 'as is' (i.e. without sorting) from Power Query into the Model (i.e. hitting "Close and Apply") and then going to the "Table View" (picture shown below) and creating a DAX expression based on the idea that Pete suggested, and doing the sort in the Table View based on that DAX expression - this is what matters at the end of the day anyways, as its closer to the 'Front-end".
Hopefully one of the suggestions works out for you
= Table.Sort(#"Removed Errors",{{"Location", Order.Ascending},{"Building", Order.Ascending},{"Employee Name", Order.Ascending},{each List.PositionOf({"Morning","Day","Ext. Day","Evening or Night","24Hour"},[Clearance]), Order.Ascending}})
