Multiple columns in gallery - Distinct filter

[luisep]

Hi everyone,

I'd like to know how I could show multiple columns in gallery if a filter by Distinct.

My scenario:

SPlist1:

Cod Entity	Number	Code	Description
A	1	C1	Description Code C1
A	2	N85	Description Code N85
A	3	FB2	Description Code FB2
B	1	C1	Description Code C1
B	2	N85	Description Code N85
B	3	FB2	Description Code FB2

Gallery1:

Items: Distinct(SPList1,Number)

My problem: the gallery shows the column 'Number' as a result of Distinct, but I also want to add the columns 'Code' and 'Description'.

Gallery would be as this:

Number	Code	Description
1	C1	Description Code C1
2	N85	Description Code N85
3	FB2	Description Code FB2

Thanks for your help.

[RandyHayes · Accepted solution]

@luisep 

Consider using the GroupBy function instead of Distinct for this.  Distinct will give you a table with the Distinct values of the column you specify...and nothing else!

GroupBy will allow you to group them as you need.

So, this for your Items property formula:

   GroupBy(yourDataSource, "Number", "Code", "Description", "_records")

Will produce the results you want.

The _records column will have all the records that match that group (at least one).

I hope this is helpful for you.

[GPronnya]

@RandyHayes Hello, thanks for your reply. Can you please advice on how to perform COUNT of such distinct rows?

and also how in gallery I can make additional labels to show values of other colums such as Record, Description.

Thanks in advance.

[mLarenas]

@RandyHayes That was the solution that i was looking for!, Thank you!!!!

[jjacquet]

This solution works well for small datasets & this question. GroupBy() is not a delegable function, so if your dataset contains >500 records (or >2000 if you increase limit) you will likely get incomplete results if you're trying to apply this solution to something larger.

I came across this while looking to do something similar against a SQL Server table with >10,000 records. This requires pre-filtering your dataset before executing the GroupBy().

https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview#non-delegable-func...

[RandyHayes]

@jjacquet 

Delegation and Record Limit are two different things.  

No function in PowerApps will ever return more than the record limit of the app.

Criteria used in filters, lookups, etc. is what is subject to delegation.  It is either delegable to the datasource or not.

However, that function will still never return more than the record limit.

GroupBy, AddColumns, ShowColumns, RemoveColumns, Distinct, Index, Shuffle, Sort, SortByColumns, Ungroup, etc. are all functions that never delegate (because there is no criteria to delegate), so they are always restricted to the same record limits as all the other functions.

[jjacquet]

@RandyHayes - of course, you're right & I realized my mistake shortly after hitting 'reply'. I apologize.

The point does remain that there's a limit to using GroupBy, but the rationale was wrong.

Appreciate your contributions to this forum!

[RandyHayes]

@jjacquet 

No worries.  And it is an important thing to know...those functions do not delegate because there is nothing to delegate, so, they do what any other function would do...only return <record max> records.

It is very common for people to try and work around delegation errors by using things like AddColumns to "shape" their data into something they can use to avoid delegation...but it is a futile effort because you are still only going to deal with the max record limit.

Always happy to contribute!! 😁