Power Apps: Dynamic Sorting by Column Headers
In PowerApps, galleries and tables do not automatically have any sort settings by default. This is something that needs to be built. In this post, I’ll explain the different aspects involved in building this solution where you can click each column heading to sort by that column, and when you hover over each column heading, that heading and sort icon lights up, as seen below with the project name column:
In this example, I use a local variable called varSortPriority. Each time I click a column heading, it is going to change the variable, and the gallery is sorted by that varSortPriority variable. I also have a variable called SortDescending, which I will be able to toggle back and forth between ascending and descending.
In this example, my data source is called Projects. So for the Items property of this gallery, I set it to the following:
SortByColumns(Projects,varSortPriority,If(SortDescending,Ascending,Descending))
This says to sort the gallery by the varSortPriority, and if the SortDescending variable exists, then sort by ascending, otherwise sort it in a descending order.
I inserted labels above the gallery in that medium shade of purple that you see. The labels themselves don’t have any special properties. I inserted a Arrows Up Down icon right on top of each of these labels, which is where the logic happens.
First of all, I named my column header labels appropriately. I’ll use the project name as an example here, I called it lblProjCol
Then, here are the properties that I set up for the sort icon that I placed on top, which I called icoSrtProj. (All of the column headers do have a sort icon, I just set it up so that it only turns that lighter purple color when you hover over it.)
| Property | Value | Description |
|---|---|---|
| OnSelect | UpdateContext({varSortPriority:”Title”, SortDescending: !SortDescending}) |
Title is the name of this column in SharePoint, even though I renamed it to Project Name later. The exclamation mark in front of the SortDescending is what makes it toggle back and forth between ascending and descending each time you click on it. |
| Color | icoSrtProj.Fill | Color is the same as fill, so it’s invisible by default. |
| HoverFill | RGBA(255, 255, 255, .5) | “lights up” when you hover over it |
| X | lblProjCol.X | same X axis as the project column label |
| Y | lblProjCol.Y | same Y axis as the project column label, so that it sits directly on top of it. |
| Width | lblProjCol.Width | |
| Height | lblProjCol.Height | |
| PaddingLeft | icoSrtProj.Width-40 | This sets the icon to have a lot of padding on the left, which makes it cover up the column header wording. This is important in making the clickable button stretch all the way across the column header. |
The same pattern is used for all of the other column header labels, putting an icon on top of each one, and setting the icon’s properties in relation to the label, and setting the sort priority to that column’s name in SharePoint. For example, the status column’s OnSelect is:
UpdateContext({varSortPriority:”Project_x0020_Status”,SortDescending: !SortDescending})
Here is the video where I demonstrated this full solution in my SharePoint Power Hour:
Have fun! Also, check out my PowerApps training classes if you’d like to get more in-depth knowledge: IW Mentor – PowerApps Training
Hello,
SortByColumns sort only in alphabetic order. How is possible sort the column with Numerical or Date value?
Thanks for answer.
I followed this approach and get a delegation warning as my datasource contains more than 100000 items.
Did you do the approach I covered in the video, where I talked about creating every column as text in SharePoint, hence less delegation issues? Microsoft has since improved it a lot, too, and date fields are the only ones that aren’t delegable. The trick is that your list *after* filtered should be down to less than 2,000 items, since that’s the PowerApps limit. You’re not ever going to be able to display a gallery of 100,000 items in PowerApps.
Hi Laura,
I have a bar chart using a sp list as a datasource.
Is it possible to sort the x axis aplhabetically on the chart?
Thank you
Sandor
You can use the Sort function in the items property, so that should apply in that case.
Thanks, that really helped!
How to add sort control if we are using container for items gallery and header .if we using flexible width in container and labels in header then if we add icons it’s taking more space and going far away from label.can you help on this
Sorting by column headers is now built in when you use a new modern table control. Other than that, I’m not sure of the answer to your question.
Hi Laura,
I’ve been trying to figure this out for so long… I tried your method above, and it’s not working. My columns are choice columns, so when I do a sort, I have to indicate it by using .Value after my column name.
Would you have any idea how I can accomplish this dynamically ? I have 7 columns in my gallery, hence I have 7 conditions in my items property because I don’t seem to be able to indicate .value after the column name.
Any help would be very much appreciated. Thank YOU !!!
In my example, the status column was a choice, and I can’t seem to replicate your issue. Also, note that the new modern table control has built-in column header sorting. In your settings, enable modern controls in order to see this one in your list of controls to add.
You’re going to run into delegation issues if you do it this way.
It depends. There are a lot of factors and options, so it’s not a definitive problem.