Forum Discussion
Sort Rows by Attributes
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 9999
Pete
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!
Hi Anonymous Following your suggestion sorting by Classification doesn't work because it sorts the table by the Classification regardless of the Location, Building, and Employee Name.
Hi Anonymous ,
Perhaps I've misunderstood your requirements - do you want the [Clearance] column to be in your chosen order within visuals in Power BI? If so, then what I've told you is correct.
Once you've added the sort key column and applied it to the model, you then select your [Clearance] column in the Data list on the right in Desktop, go to the Column Tools tab > Sort by Column and select your new sort key column to sort by.
If you're expecting any sort orders that you apply in Power Query to persist into Desktop once you apply the queries to the model, then don't. The VertiPaq engine sorts and orders columns however it needs to in order to maximise data compression, so you can never guarantee your columns will be where you put them or in the same order as in Power Query.
Pete