Combobox Not showing all filtered options from an excel table

[Anonymous]

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 filter

Relationship between the two combobox

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

[RandyHayes]

@Anonymous 

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.

[Anonymous]

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.

[RandyHayes]

@Anonymous 

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.

[Anonymous]

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

[RandyHayes]

@Anonymous 

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.

[Anonymous]

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

[RandyHayes · Accepted solution]

@Anonymous 

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.