Enhancing Your Power App: Filter a Gallery by Year

Posted on March 14, 2024 by WonderLaura Leave a comment

Hey there, Power Appers! Do you want to learn how to create a cool feature for your app that lets you filter a gallery by the year of the items created? In this blog post, I’m going to show you how to do just that, create a Power Apps gallery with a filter drop-down. It’s super easy and fun, and you only need a few steps to make it happen. For large lists in SharePoint, filters are an imperative concept when it comes to delegation warnings.

Before we get started, you will need to have a Power App and a list. For this example, I’m going to use a SharePoint list called “Projects” that has a column called “Created” that stores the date each item was created. Every list and library has this column. You may use any data source that you like, as long as it has a date column.

Create a blank app and add a Power Apps gallery

First, we need an app and a gallery that will show the data from our data source. I’m starting with a Power App that has a list of projects already added as a data source. Here’s how you do it:

1. On the left panel, click on Insert

2. Choose the Vertical Gallery.

3. Resize and position the gallery as you like. For the Items property, use Projects, which is the name of the list.

4. On the right panel, click on “Fields” and select the fields that you want to show in the gallery (I’m going to use “Title” and “Created” for this example).

That’s it, you now have a gallery that shows the data from your data source. You can preview your app by clicking on the play button on the top right corner.

Add a drop-down box and populate it with years

Next, we need to add a drop-down box that will show the unique years from the date column of our data source. I’ll show you how to do this dynamically, so that you don’t need to manually change the list of years in the app each year.

5. Click on the App in the tree view, and go to the OnStart property.

6. Enter this formula:
ClearCollect( colYears,
{Year: Year(Now())}, {Year: Year(Now()) – 1}, {Year: Year(Now()) – 2}, {Year: Year(Now()) – 3} )

7. When the app starts, this collection will automatically be created, with a list of this year and the previous three years. Add as many records as you’d like, depending on how many years’ worth of data you need to interact with in the app. Right click App on the left, and choose Run on Start, to generate this collection.

8. Click the Insert button, and insert a drop down box and drag it to the top of your screen above the gallery.

9. Rename it to drpPickYear.

10. Use the following properties for this drop-down box:
Items: colYears
DefaultSelectedItems: {Year: Year(Now()) }

11. In the properties panel on the right, click the Edit button next to fields, and use the Add field button to add the Year field, like this:

Now when you click to preview your app, you’ll see that the drop-down looks like this:

Filter the Power Apps gallery by the selected year

Finally, we need to filter the gallery by the year that the user picks from the drop-down box. I’ve set mine to default to this year, so that the list is always filtered. Here’s how you do it:

12. Select your gallery, and in the formula bar at the top, and go to the Items property. In the formula bar, enter the following:

This expression will filter the Projects data source by the Created column, where the year matches the selected value from the drpPickYear drop-down box.

Click on the play button to preview your app and see the gallery filtered by the selected year from the drop-down box.

Awesome, you have successfully created a drop-down box that filters a Power Apps gallery by the year of the items created in SharePoint. You can now save and publish your app and share it with others. Here is a video that I recorded yesterday, showing how to accomplish this. I also teach about the concept of delegation in Power Apps, and demonstrate how to add multiple drop-down boxes for filtering by various columns.