How to Auto populate a drop down filter from a list with more than +500 records

[ChristianDK]

Hi there,

I have some long lists which are populate a bit random to say at the least, therefore it will be very hard to hardcode the dropdown filters.

I am building some test structures and something works and other do not.

I want to used some nice drop down filter to sort the data.

If I populate the drop-down menu in hand, everything is fine:

Juni is selected and I can get list item 971 ect..

Now I want to populate the drop-down menu automatic from the list

And I want to be distinct so I do not get a tons for the same months.

The month here are in the column "Title"

So I try with Distinct:

And the drop-down is not populated after the first 500 lines..

And I seem not to be able to Filter and then call Distinct

This is not working...

Maybe I am attacking this wrong.. I hope you  can help. Many Thanks.

[Drrickryp]

@ChristianDK 

The items property of the dropdown should be 

Distinct(test_of_1000_lines,Title)

This will create a single column table of unique values of Title.  PowerApps will rename it Result.  So to sort the list, the items property of the dropdown would look like this:

Sort(Distinct(test_of_1000_lines,Title), Result, Ascending)

And the items property of a gallery filtered for the dropdown would look like this

Filter(yourlist,Title=Dropdown1.Selected.Result)

The reason you are not getting more than 500 is that Distinct() is not delegatable.  There are two ways to deal with this, you could increase the delegation limit in the app's advanced settings from 500 to 2000 and then all the data would be used if you had 1000 items in your list. 
You could also randomize your list using the Shuffle function and likely the resulting list would include one of each Title.  That would look like this.

Sort(
     Distinct(
               Shuffle(
                        test_of_1000_lines
                ),
                  Title
      ), Result, Ascending
)

I use the Shuffle() function when my lists are > 2k .

[ChristianDK]

Thank you for the 3 first lines, they are very useful and exoplanetary.

I like the shuffle() idea but I do have to make sure, that there are not 1000 lines A but just one line B in my list...

I was considering if i should do some sort of auto populate another list in share-point and then use that for the drop down values so I am sure I got them all correct.

[Drrickryp]

@ChristianDK 

You can export your list to excel and remove the duplicates with power query and then bring the table back in as a datasource and use it to populate the dropdown.