cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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/
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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,818)