Unanswered

Distinct for large Dataverse table

(0) Share

Report

Posted on by AlexTou

Hey,

I just started playing around with Dataverse and trying to find out well large Datasets work in a Powerapp.
So: I have some toy data with 50k rows or so. Each entry has a random country in the "location" column.

I'd like to create a Dropdown with all possible countries and filter my data by this selected country.

Here's the problem, I tried setting the Items of my Dropdown to:

Distinct(myData, location)

This is the way you'd expect it but due to Disctint not being delegable, this only returns the distinct location values for the first 2000 entries. How do I get all the distinct values?
(Just to clarify: These are real countries, so there should be around 200, I think)

I thought I might be able to set up a view in Dataverse with either Distinct or GroupBy but I can't find out how to do that either...

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @AlexTou ,

I see your challenge. My recommendation is to create a Countries table (or Choice if desired) in Dataverse and add the lookup (or choice column) to the table in Dataverse. This will allow you long term to see how many records by Country and use the Country in other tables as well maintaining the relationships. After doing this then you can just populate the dropdown with the value from the Countries table (or Choice).

Was this reply helpful? Yes No
AlexTou 6 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann and thanks for your answer.
How would I do that though? I mean, it's easy enough to create a table called countries. But I can't populate this with a Distinct() on the other table's column... Or at least I don't know how...

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @AlexTou

What I mean is you would do the following:

1. In Dataverse, export your rows from your data table into Excel and identify the unique country names (through remove duplicates). Might take a few exports with 50K rows :).

2. Once you have all the duplicates removed then use these distinct rows to populate a "Country" table. You should be able to do this through an Excel Import...

3. Add lookup column to the original table for the Country field (keeping current Location field for now)

4. Create a view with the Name, Location and new Country field for your original table

5. Export the original table records to Excel with that view and then take the values in the Location column and populate them to the "Country" column

6. Import the records and as long as the Name field on the Country table matches the value it will populate the lookup.

Hopefully this makes sense.

Was this reply helpful? Yes No
AlexTou 6 on at

Like (0)

Report
Copy link

Link copied!

Thanks again.
Sadly, this isn't a great option. This would work for my toy data set but for any real data where data gets added all the time this isn't viable.
I just don't understand how something so simple isn't a default possibility as a view...

Was this reply helpful? Yes No
Andy3 7 on at

Like (0)

Report
Copy link

Link copied!

I guess you could run a data flow that pulls the distinct countries from your main data and pushes it to a countries table (similar to previous suggestion but automatic). It's just a quick bit of power query to do that, but I agree still not ideal. I'm similarly finding row limits in PowerApps very frustrating! Most business systems need access to more than a couple thousand rows, come on Microsoft!

Was this reply helpful? Yes No
atireg 2 on at

Like (0)

Report
Copy link

Link copied!

Please try the below

Upper(Sort(Distinct(Filter(myData,StartsWith(location,Self.SearchText)),location),Result))

Was this reply helpful? Yes No