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!

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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