Sort Rows by Attributes

[work_1111]

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}})

[wdx223_Daniel]

= 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}})

[crm2710]

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

[BA_Pete]

Hi @work_1111 ,

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

[work_1111]

@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}})

[BA_Pete]

Hi @work_1111 ,

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

[crm2710]

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!

[work_1111]

Hi @crm2710 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.