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!