Answered

Search filter not working on Excel data source in Canvas App

(2) Share

Report

Posted on by CU10030108-0

I am building a Canvas App in Power Apps connected to an Excel/OneDrive data source. I have a gallery/table with a search input field and I am trying to filter records by Program and School columns when the user types in the search box.

I tried the following formula on the Items property of my Table:

Filter('staff leave',
SearchInput3.Text in Program ||
SearchInput3.Text in School
)

I also tried:

Search('staff leave', SearchInput3.Text, "Program", "School", "Title")

The formula shows an error when filtering the records when I type in the search box. The columns I want to search: Program and School.

What is the correct formula to search/filter across multiple text columns in an Excel-connected Power Apps

Any help would be greatly appreciated

Thank you

Categories:

Building Power Apps

Power Query

I have the same question (0)

All responses (3)

Answers (1)

Suggested answer

11manish 769 on at

Like (1)

Report
Copy link

Link copied!
Use Filter + StartsWith (Most Reliable)

Filter(
'staff leave',
StartsWith(Program, SearchInput3.Text) ||
StartsWith(School, SearchInput3.Text)
)

Works well with Excel

Delegation-friendly (partially)

No errors

Thanks
Manish

Visit my blog : https://powerappbuilder.blogspot.com/

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,777 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @CU10030108-0,

Firstly, Excel is really the "data source of last resort" and also storing the file in OneDrive will not allow sharing with others - you need to move it to a SharePoint Library. My first guidance I will offer is to put your data in a SharePoint List as this will at least get some consistant performance.

Also Excel is not Delegation-friendly at the best of times, so if you have less than 500 records (you can increase this to 2,000 with your Data Row Limit ), you can do this (note that quotes "" are not required around the field names - this was changed some years back)
With( {_Data: 'staff leave'}, Search( _Data, SearchInput3.Text, Program, School, Title ) )

This effectively uploads the entire Table and then performs the Search "locally", eliminating any Delegation dependancy.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
WarrenBelz 154,777 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @CU10030108-0,

A quick follow-up to see if you received the answer you were looking for. Happy to assist further if not.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No