cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vijayaprakash
Regular Visitor

Search data from excel by selecting date in a date picker

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

1 ACCEPTED SOLUTION

Accepted Solutions
GarethPrisk
Resident Rockstar
Resident Rockstar

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!

View solution in original post

2 REPLIES 2
GarethPrisk
Resident Rockstar
Resident Rockstar

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
Regular Visitor

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (1,909)