, ,

PowerApps: Filtering a SharePoint List By Date

PowerApps Filter SharePoint List By Date Cover image

Microsoft PowerApps is a powerful low-code platform which allows you to connect to hundreds of data sources - SharePoint is one of them and a very popular choice in enterprises. A very common thing to implement is a list of items respectively records of a data source - in this example a SharePoint list. This works by navigating to Insert → Gallery → Vertical and adding the SharePoint list as data source as shown in the official documentation.

Having inserted the gallery including the connected SharePoint list, let's try to filter the list by a date. For our example, we will use the following elements in order to create an exmaple:

  • Gallery with the SharePoint list items → Gallery
  • SharePoint list → SharePointList
  • Selected date from date picker in PowerApps used for filtering → DatePicker.SelectedDate
  • SharePoint list date (field to filter at SharePoint side) → SharePointListDate

Using the Filter() function, we can create the following formula and insert it on the Gallery.items property:

Filter(SharePointList, SharePointListDate = DatePicker.SelectedDate)

However, this formula which works in almost every other case in PowerApps doen't work when filtering a SharePoint List by date! It doesn't work to directly pass a date into the filter formula. Instead, we have to pass the day, month and year separately into the formula.

It works like this by adding the following formula to the Gallery.items property:

Filter(SharePointList, SharePointListDate <= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)))

Please keep in mind that the above displayed formula only works for dates which are equal or larger than SharePointListDate. In order to create the "ultimate filtering formula" for filtering for one exact datewe have to create a second filtering criteria:

Filter(SharePointList, SharePointListDate = Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)))

As you can see, this isn't a very professional way of filtering a SharePoint list by date. However, as far as I am aware, there isn't any other possibility a this point of time and the presented workaround is the only way to go.

6 responses to "PowerApps: Filtering a SharePoint List By Date”

  1. Joel Avatar

    You absolute legend!

    1. Lennart Wörmer Avatar

      Thanks. Happy I could help you!

  2. april avatar

    Hi there
    I'm trying to filter a list to show only items where the date in column Start Date = Today

    Any ideas/

    1. Lennart Wörmer Avatar

      Hi, you can compare the SharePoint date field with Today() in Canvas Apps. You can start with Filter(SharePointList, StartDate = Today()) function (you may have to adjust it to your needs). Take care that if the date field includes a time you also have to adjust the filter to only filter the date but not the time.

  3. Tom Avatar

    Didn't include sorting. Sometimes you want the latest date value from the list item.

    I figured it out though:

    AddColumns('Validations List', "ValDate",'Validation Date'),Title=txtWorkID.Text
    Validation Date

    If you have a text box called txtWorkID, you can fill the box with an ID and if it matches an ID in the Title field (which you can safely rename "WorkID", but don't have to), it will bring back all dates for that ID, sorted in descending (latest, first) order.

    1. Lennart Wörmer Avatar

      Thanks for your feedback, Tom! Glad you found a way to figure it out!

Leave a Reply

Your email address will not be published. Required fields are marked *