Search data from excel by selecting date in a date picker

[Vijayaprakash]

Hello,

I have connected an excel table named “LIFT_DETAILS” in powerapp. I have the following columns in

1. Block No
2. Lift ID
3. Building Name
4. Turned OFF Date
5. Turned ON Date

Now I want to create search page based on Turned OFF date, so I have inserted a date picker and a vertical gallery with Title (Block No & Lift ID), sub title (Turned OFF Date) and body (Building Name)

If I choose any date in the date picker then if any of the lifts are turned OFF on that selected date it must be shown in the vertical gallery. Else the vertical gallery should show a message as “No Turn OFF on selected date”

Kindly please guide me on how to make it. Thank you

[GarethPrisk · Accepted solution]

A few questions:

1. Did you add the Excel as a static table?
2. Otherwise, did you use the OneDrive connector, or the Excel online connector?

   This depends on the formatting of your Turned OFF column. That in turn, can be impacted by your data source.

   Let's assume the column is formatted as a Date column within Excel. PowerApps will ready this column with a timestamp, so you will need to adjust your filtering to use only date (not time) parts.

   • Before filtering, see how Power Apps is reading your date columns
     • You may have 1/1/2020 in your file
     • PowerApps may be reading it as 12/31/2019 7:00PM (offsetting from UTC)
     • Keep in mind, the DatePicker will output a SelectedDate value with a timestamp

You can do one of the following:

If the timestamp is consistent in Excel data, then you can simply adjust your DatePicker's SelectedDate output to match

Filter(
    excelData,
    excelDateColumn = DateAdd(
        DatePicker1.SelectedDate,
        TimeZoneOffset(DatePicker1.SelectedDate) * -1,
        Minutes
    )
)

Or adjust the source data to match the DatePicker

Or break the dates on either side into their parts

Filter(
    excelData,
    Date(
        Year(excelDateColumn),
        Month(excelDateColumn),
        Day(excelDateColumn)
    ) = Date(
        Year(DatePicker1.SelectedDate),
        Month(DatePicker1.SelectedDate),
        Day(DatePicker1.SelectedDate)
    )
) 

Definitely a few idiosyncrasies with dates and filtering. Good luck!

[Vijayaprakash]

The excel file is located at the onedrive. I tried the first syntax given by you. It works well based on my requirement.  Thank you so much for your support. 🙏