Search data from excel by selecting date in a date picker

(0) Share

Report

Posted on by Vijayaprakash

Hello,

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

Block No
Lift ID
Building Name
Turned OFF Date
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

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

GarethPrisk 2,828 on at

Like (1)

Report
Copy link

Link copied!
A few questions:

Did you add the Excel as a static table?

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!

Was this reply helpful? Yes No
Vijayaprakash 5 on at

Like (0)

Report
Copy link

Link copied!

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. 🙏

Was this reply helpful? Yes No