Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
work_1111
Helper II
Helper II

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 7
wdx223_Daniel
Super User
Super User

= 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
Advocate I
Advocate I

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". 

 

crm2710_0-1708360602955.png

 

Hopefully one of the suggestions works out for you

 

 

 

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors