cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PAB
Advocate III
Advocate III

Count the times a value Appears within a column into a collection

Hi,

 

I have a collection that has a column labeled "Status" and I would like to collect the amount of times each value appears in this column.

 

The values for the column status are:

Covered

Covered (PA/ST)

Not Listed

Not Covered

Visco Blocked

Step Other Visco

 

I would like to collect this into a new collection labeled "Sum of Status"

Here is what I want the collection to look like:

StatusCount
Covered4
Covered (PA/ST)2
Not Listed6
Not Covered2
Visco Blocked0
Step Other Visco1
1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

You can use the functions GroupByAddColumns and ShowColumns to accomplish that. If your collection is called 'coll', you can calculate the number of items in a collection called 'coll2' using the formula below:

ClearCollect(coll2,
    ShowColumns(
        AddColumns(
            GroupBy(coll, "Status", "ByStatus"),
            "StatusCount",
            CountRows(ByStatus)),
        "Status",
        "StatusCount"))

To break it down: GroupBy creates a "grouped table" based on the value of the status column

GroupBy(coll, "Status", "ByStatus")

Then we add another column to that table that contains the number of items for each status:

AddColumns(
    GroupBy(coll, "Status", "ByStatus"),
    "StatusCount",
    CountRows(ByStatus))

Finally, we select only the columns that we want (status and total number of items with that status)

ShowColumns(
    AddColumns(
        GroupBy(coll, "Status", "ByStatus"),
        "StatusCount",
        CountRows(ByStatus)),
    "Status",
    "StatusCount"))

At the end, that table is collected into the new collection.

 

Notice that it will only show the number of status of items that actually do have a value. In your case, you'll not get an entry for the "visco blocked", since there are no entries with that status. You can work around this by creating a table with all possible values, then do a look up on the grouped table, using 0 if no such items exist. One way to do that would be to use the Coalesce and LookUp functions, as shown below:

ClearCollect(coll3,
    RenameColumns(
        AddColumns(
            ["Covered", "Covered (PA/ST)", "Not Listed", "Not Covered", "Visco Blocked", "Step Other Visco"],
            "StatusCount",
            Coalesce(LookUp(c2, Status = Value).StatusCount, 0)),
    "Value",
    "Status"))

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I don't have my computer in front of me to test this but it sounds like you need the Group command with a Counta

Microsoft has an example here with Group and Sum in a collection.

https://powerapps.microsoft.com/en-us/tutorials/function-groupby/
CarlosFigueira
Power Apps
Power Apps

You can use the functions GroupByAddColumns and ShowColumns to accomplish that. If your collection is called 'coll', you can calculate the number of items in a collection called 'coll2' using the formula below:

ClearCollect(coll2,
    ShowColumns(
        AddColumns(
            GroupBy(coll, "Status", "ByStatus"),
            "StatusCount",
            CountRows(ByStatus)),
        "Status",
        "StatusCount"))

To break it down: GroupBy creates a "grouped table" based on the value of the status column

GroupBy(coll, "Status", "ByStatus")

Then we add another column to that table that contains the number of items for each status:

AddColumns(
    GroupBy(coll, "Status", "ByStatus"),
    "StatusCount",
    CountRows(ByStatus))

Finally, we select only the columns that we want (status and total number of items with that status)

ShowColumns(
    AddColumns(
        GroupBy(coll, "Status", "ByStatus"),
        "StatusCount",
        CountRows(ByStatus)),
    "Status",
    "StatusCount"))

At the end, that table is collected into the new collection.

 

Notice that it will only show the number of status of items that actually do have a value. In your case, you'll not get an entry for the "visco blocked", since there are no entries with that status. You can work around this by creating a table with all possible values, then do a look up on the grouped table, using 0 if no such items exist. One way to do that would be to use the Coalesce and LookUp functions, as shown below:

ClearCollect(coll3,
    RenameColumns(
        AddColumns(
            ["Covered", "Covered (PA/ST)", "Not Listed", "Not Covered", "Visco Blocked", "Step Other Visco"],
            "StatusCount",
            Coalesce(LookUp(c2, Status = Value).StatusCount, 0)),
    "Value",
    "Status"))

View solution in original post

Thank you so much for your detailed reply, this worked perfectly for what I was trying to do.

n/a

@CarlosFigueira thanks (even though this was a long time ago).

 

Still very helpful pattern and very well explained. Much appreciated!

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,640)