cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexclavijoN
Frequent Visitor

Combobox Not showing all filtered options from an excel table

I have a main window where you can choose a text value, this filtered value will be connected to a combobox dropdown. But when i go to the combobox window, it is not showing all the related values to the first filter. i have 57 rows on the excel table but the combobox shows only 37 rows.

 

first filterfirst filter

 

Relationship between the two comboboxRelationship between the two combobox

please help me, how can i make the combobox show all the filtered values.

1 ACCEPTED SOLUTION

Accepted Solutions

@AlexclavijoN 

I had assumed you were using OneDrive For Business now, as that is how you select an Excel file to be a datasource.

BUT, here is the real issue.  No datasource will currently support the number of rows you have.  This really has everything to do with the limits in PowerApps.  If you look at your advanced settings, you will see the maximum number of rows to return.  By default it is 500.  You can bump it up, but the maximum is 2000.  

So - you MUST rely on delegation to get your results.  AND, Excel is NOT a delegable datasource, so there is nothing you can do to overcome that limit unless you break the table in excel up into multiple tables that are no bigger than 2000.

This means you really need a delegable data source to 1) delegate your filters and other functions or 2) delegate enough queries to fill a local collection with all the rows so you can work on it locally. 

Both of those require a delegable datasource (which Excel is not).  

 

As for choices - If your organization is Office365 based, then you already have the ability to use a SharePoint data source.  No additional fee.  If you move up to SQL, then you will need a SQL data source - lowest cost option is a low-end SQL Azure datasource.  

With both, you will still not be able to get all your rows in one query, but they are delegable, so you have more options to delegate your queries to them.

 

I hope this is clear and helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

7 REPLIES 7
RandyHayes
Super User III
Super User III

@AlexclavijoN 

Are you perhaps having issues with letter case or extra space?

Perhaps try this formula 

Filter(cds; Upper(Trim('Línea Gráfica')) = Upper(Trim(ComboBox1.Selected.'Nobre circuito').Nombre

This will make the case all the same and trim any extra spaces.

 

I hope is helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Nope, they have the exact same name, so spaces an upper cases shouldn't be a problem. And i tried the formula and does not work :/. i was thinking that maybe it was problem on the excel about the size that powerapps can handle but i don't know if that could be a problem.

@AlexclavijoN 

Well...how large is the Excel file?  You had mentioned 57 rows (which is definitely not an issue), but if you have a large number of them, then yes, it could be a factor.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

mmm i have 3 columns with 15000 rows each more less. If this were the problem ¿how can i solve that?

@AlexclavijoN 

Oh yes!! That is the issue!  Actually, I'm not entirely sure what the current Excel row limits are as we rarely work with Excel as a large data source.  But, it seems to me, given the other data source limits, that 15000 would be beyond that.

There is not much you can do to delegate formulas to the Excel datasource as it is a non-delegable source. 

You would have to somehow pull in all of the rows into your app to start with into a collection and then filter against that collection.

If the Excel file does not change (which I suspect it does considering the rest of your descriptions), then you could do an Excel import data connector.  But, then it would be static data.

For something of this size, you should consider other data source options such as SQL or even SharePoint.  Either one, but, with both, you would start with a design that provides easy delegation for your PowerApp.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Can Onedrive for bussiness be a solution? i don't think i can use sharepoint

@AlexclavijoN 

I had assumed you were using OneDrive For Business now, as that is how you select an Excel file to be a datasource.

BUT, here is the real issue.  No datasource will currently support the number of rows you have.  This really has everything to do with the limits in PowerApps.  If you look at your advanced settings, you will see the maximum number of rows to return.  By default it is 500.  You can bump it up, but the maximum is 2000.  

So - you MUST rely on delegation to get your results.  AND, Excel is NOT a delegable datasource, so there is nothing you can do to overcome that limit unless you break the table in excel up into multiple tables that are no bigger than 2000.

This means you really need a delegable data source to 1) delegate your filters and other functions or 2) delegate enough queries to fill a local collection with all the rows so you can work on it locally. 

Both of those require a delegable datasource (which Excel is not).  

 

As for choices - If your organization is Office365 based, then you already have the ability to use a SharePoint data source.  No additional fee.  If you move up to SQL, then you will need a SQL data source - lowest cost option is a low-end SQL Azure datasource.  

With both, you will still not be able to get all your rows in one query, but they are delegable, so you have more options to delegate your queries to them.

 

I hope this is clear and helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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