Search and then filter results from Excel data source

[Petecmsmith]

Hi all,

New caller long time listener.

A real newbie with PowerApps trying to create a really useful app for my team during this current climate and downturn in actual work.

The idea
Using a spreadsheet held in OneDrive I have over 2000 rows of questions and answers (plus fields for various other bits of info) that I want to create my app to interrogate. I want to allow my users to search for keywords throughout the data and once provided with the results to be able to refine using drop-down filters based on the other info held in the table.

So far

I have the data source set up and connected and an app that does search and provide results which we can expand on and edit and update. All good. However, I have noticed that I'm possibly not getting 100% accurate results as there are some keywords that I know exist that I cannot bring up in results. Is there a limit to the amount of data in Excel?

Also I am struggling to work out how to then apply filters to the results, is there an example somewhere you can point me to?

Thank you and stay safe

[eka24]

If you have Over 2000 rows, you would face dlelgation issues. Meaning your filter results would not show some of the records as you have observed. If the Questions and Answers do not change regularly, I suggest ypurather import the excel:

This will enable you filter all the data and also enhance the performance.

You can read for more on delegation:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[mdevaney]

@Petecmsmith 
I had a chuckle at your opening line "long time listener, first time caller".  You have a great sense of humor!

Unfortunately, PowerApps has a limit of 2,000 rows when using OneDrive + Excel as a datasource.  Any filters applied will not retrieve data past the 2,000th row.  When most people encounter this situation they begin to consider SharePoint as the next logical place to store data for their app which is also free.  Sometime to consider though is SEARCH cannot be delegated in both Excel or SharePoint: we can only search the start of a word using the StartsWith feature.

If your questions are 'static' meaning that they won't ever change you can always try the import from Excel connector.  That can handle as many rows as you like.  The downside is you have to re-publish the app everytime there are new questions so its not great from a read-write perspective and you can't ensure that users are always on the same version of the app if they haven't synced lately.

Let me know what other questions you have.  I'm always happy to help a PowerApps newbie 🙂

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[timl]

Ditto what @mdevaney says - welcome to the forums @Petecmsmith!

[WarrenBelz]

Endorsing @mdevaney as well.

One thing to watch is you are looking at importing your Excel to SharePoint is that if you use the wizard in SharePoint with New > App > Import Spreadsheet, it does not create a custom list that you can add a PowerApps edit form to (mistake we made some time ago).

You are better served creating the Custom list with matching fields/order, making a datasheet view and do a copy/paste from Excel.

[v-xida-msft]

Hi @Petecmsmith ,

Could you please share a more details about the Filter formula used in your canvas app?

Based on the issue that you mentioned, I think you have faced a Delegation warning issue. The Excel table is not a Delegable data source in PowerApps, in default, it could only process first 500 records from your Excel table data source. You could change this limit to maximum value -- 2000, then you could process 2000 records at most in your app.

Please consider set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then check if the issue is solved.

If the desired record is 2001 or more, I think the Filter function could not achieve your needs. As an alternative solution, please consider bulk-load your Excel table records into a collection in your app, then use the collection as data source in your Filter function.

Please check and see if the following thread would help in your scenario:

http://powerappsguide.com/blog/post/how-well-does-powerapps-cope-with-large-excel-files

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Best regards,

[Petecmsmith]

Thank you both. I have overcome the limit on delegation rows for the powerapps filtering by changing the advanced settings.

I wonder if I create a table per product set (this is essentially what I am searching for records in) this will overcome the issue as I won't reach 2000 rows per product for some time.

So my user journey from opening the app would be:

Select the product set to be searched (a separate table for each)

Then use a free text entry box to search keywords (as I already have that seems to be working fine)

Then to Filter the results by up to three drop-down filters and one date range selector. I will create the drop-downs from a new table in my data source using data validation in Excel and there is a created on field within my table that I need to query.

I am not wedded to that order of events if it makes sense to create a collection first?

FYI my filter for the current search I have working is

Filter (Table1, TextSearchBox1.Text in Question, Response, Tags, Feature)

[eka24]

If you can separate each product in a table and wont affect the Objective, you are good to Go. Try not to hit the delegation limit.

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-xida-msft]

Hi @Petecmsmith ,

I agree with your thought almost. You could consider create separated Excel Table for each Product. Then within your app, create a connection to these individual Product Excel tables (ProductA_Table, ProductB_Table, ProductC_Table, ...).

Within your canvas app, you could add a Dropdown (called "ProductDropdown") to list all available Products. Set the Items property of the Dropdown to following:

["ProductA", "ProductB", "ProductC"]

Then set the Items property of the Gallery to following:

Filter(
       If(
          ProductDropdown.Selected.Value = "ProductA",
          ProductA_Table,
          ProductDropdown.Selected.Value = "ProductB",
          ProductB_Table,
          ProductDropdown.Selected.Value = "ProductC",
          ProductC_Table
       ), 
       TextSearchBox1.Text in Question ||  TextSearchBox1.Text in Response || TextSearchBox1.Text in Tags || TextSearchBox1.Text in Feature
)

Best regards, 

[Petecmsmith]

This is excellent, thank you so much.

One more thing - from the results I can click on these to be taken to the details (from which I can edit or delete), the Details screen is still linked to my original table. Now I have all my product tables how do I pull through the results to this (pull through the table containing the results from the gallery on the first page)?