Combobox only shows 500 items from excel table

(0) Share

Report

Posted on by warang

Hi experts,

I have the Excel spreadsheet uploaded to SharePoint and we regularly update the list.

I added this Excel file using Excel Online Data which is called Table1 and I chose Table1 as the item for combobox. However, it only shows 500 items. Currently, the Excel file has 588 rows and I have confirmed this by using label text to display (CountRows(Table1)) so there is no issue calling spreadsheet within the Powerapps.

Can you please advise the best way to fix this? Is there a way that I can display all items within combobox? I saw the other post saying use the gallery instead but depending on the combobox selection, it also changes the other field too so I want to keep it as is if possible.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

PedroK 69 on at

Like (0)

Report

The search option only search until 500 elements. (i see that you're just counting but... Maybe related?)
Maybe that is your issue...

Was this reply helpful? Yes No
warang 25 on at

Like (0)

Report

but when I click my combobox, it shows all list of items in a dropdown list but it only shows 500 items...I turned off the search option but still the same

Was this reply helpful? Yes No
v-qiaqi@microsoft.com on at

Like (0)

Report
Hi @warang,

Are there distinct value in your dropdown list, if there are, you could use the Distinct() function to deal with it.

Distinct(Table1,ColumnName)

If there is not, Search is the only way to access more items.

Or try to collect the items into a collection, populate it with the collection, see if it works.

Was this reply helpful? Yes No
warang 25 on at

Like (0)

Report

I think that's the first issue I have faced before. When I use distinct, it does not show anything in the dropdown list.

Then I chose Data name called Table1 as a datasource and it shows but only 500 items. Should I use the Collection in this case? Where is the best place to put collect function?

Thanks

Was this reply helpful? Yes No
v-qiaqi@microsoft.com on at

Like (0)

Report
Hi @warang,

Not sure how you configure the field of this ComboBox, which is important.

And, collection maybe do the trick, you could set the OnVisible property of the current screen as:

ClearCollect(Items,Table1.FieldName)

Remember to switch to another screen and switch back to this screen to generate this collection.

Was this reply helpful? Yes No
warang 25 on at

Like (0)

Report

Hi Qi,

Thanks for your help however, it still does not show anything when I tried your solution. I didn't configure anything in the field of this ComboBox. Just have changed the Items field as your solution above.

So when I chose Table1 as a data, it works but only shows 500 items. When I tried Distinct or above solutions you provided, it is not showing anything in drop down list.

If you want to know any other configuration, please let me know.

Thanks

Was this reply helpful? Yes No
v-qiaqi@microsoft.com on at

Like (0)

Report

HI @warang,

Well, it is the limitation.

"When I tried Distinct or above solutions you provided, it is not showing anything in drop down list. "

You should show me how you con figure the field, even you don't configure, I need this kind of details.

Sometimes, a screenshot is betters than words.

What's more, I have mentioned that if you have tried collection and other ways, that does not make it. Search is the only way to access more items, seach items from dropdown list in ComboBox.

Further, I don't suggest Excel table on ExcelOnline.

Was this reply helpful? Yes No