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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,805)