Unanswered

Sort by Occurences

(0) Share

Report

Posted on by cmitchener

I have a gallery that represents the distinct values from a SharePoint list's column, listing names of companies. I would like to sort this gallery by the number of times the name occurs in the column, so that the company that is listed the most is on top, the one listed the least is on the bottom, etc. How would I go about doing this?

Categories:

Building Power Apps

All responses (9)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@cmitchener

Use GroupBy - not distinct!!

i.e.

Sort( AddColumns( GroupBy(yourList, "CompanyName", "_data" ), "_count", CountRows(_data) ), _count, Descending )

I hope this is helpful for you.
cmitchener on at

Like
a
(0)

Report
Copy link

Link copied!

I think this will work, I am not getting any errors now
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@cmitchener

If the column name has a space it will not make a difference. Normally you would single-quote a column name (or any name) that has a space in it, but the GroupBy expects column names as strings, so it needs double-quotes.

What error are you getting?
AaronKnox 514 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
In Gallery.Items I'd try a GroupBy, wrap it in an AddColumns to determine number of occurrences, probably by a CountRows, then wrap the whole thing in a SortByColumns. I use a similar method to extract a 'Sort Order' column:
SortByColumns( AddColumns( GroupBy( 'Risk Mgmt Table - MASTER LIST', "Title", "_groupTable" ), "NewSortColumn", First(_groupTable.'Sort Order').'Sort Order' ), "NewSortColumn", Ascending )

You would want something like:
SortByColumns( AddColumns( GroupBy( 'YourList', "companies", "_groupTable" ), "_occurrences", CountRows(_groupTable.ID) ), "_occurrences", Descending )
cmitchener on at

Like
a
(0)

Report
Copy link

Link copied!

The error I was getting was because for text labels in the gallery, I was trying to pull ThisItem.Result, which was made for the Distinct function. The first code is working good for this part
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@cmitchener

Yes, the references to ThisItem.Result will no longer be valid as you are now dealing with the actual record columns, not the ones from a Distinct function.

Just change the Result to the column name you want i.e. ThisItem.CompanyName, or if a space in it, then ThisItem.'Company Name'
cmitchener on at

Like
a
(0)

Report
Copy link

Link copied!

Yep, that worked! My only issue now is the gallery was being sorted by a collection, shown below:

ClearCollect(colStatsAllLenders, 'Stolen Loan Checklist'.'Current Lender');
Clear(colStatsCurrentLender);
ForAll(colStatsAllLenders, ForAll(Split('Current Lender', ","), Collect(colStatsCurrentLender,{'Current Lender':Result})));

The reason for this is because the 'Current Lender' column could contain multiple names and I only wanted each company to be listed once. This code separated the companies by the commas and did so, but this led to my issue here with the sorting; but now all of the names are grouped back like the original record:

Any thoughts?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@cmitchener

Yes, but you are using the ForAll backward in you formula! You are trying to use it like a ForLoop in some development language - which PowerApps is not. ForAll is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.

Beyond that...can you explain more about what you mean by "the gallery was being sorted by a collection"??

And how does 'Current Lender' fit into things? Not seeing where that is coming from.
cmitchener on at

Like
a
(0)

Report
Copy link

Link copied!

I apologize, I had to leave for the day. Current lender is the column that holds the names, Stolen Loan Checklist is the SharePoint list. What I mean is that the three lines of code I showed above filtered the data in such a way that the entries that have multiple names in the Current Lender column had those names separated into different spots in the gallery, allowing me to see each name separately (as shown below). I may be doing something wrong, but the code I replaced it with only accepts the direct data source, not the collection I defined above. My ultimate goal is to get the data to look like the picture below, while also sorting in by the number of occurrences.