cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jawalley
Regular Visitor

Create Table counting rows of each type

I have a Sharepoint list of data, and I want to have a table in my canvas app with the number of each type of row. 

 

So my Sharepoint list would be like 

SuitNumberOtherInfo
Hearts3blahblah
Hearts4blahblah
Hearts5blahblah
Spades3blahblah
Spades9blahblah
Diamonds2blahblah

 

And I want my app to show 

SuitCount
Hearts3
Spades2
Diamonds1

 

I have a table with the first column, but I am stuck adding the Count column. 

Distinct(Filter(cardDeckSharepointList,Not(IsBlank('Suit'))),'Suit')

 

I think I should use AddColumns and Countif, but I can't figure out how to work it all together. 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@jawalley 

Please consider changing your Formula to the following:

AddColumns(
    GroupBy(
        Filter(cardDeckSharepointList,Not(IsBlank(Suit))),
        "Suit",
        "_data"
    ),
    "Count", CountRows(_data)
)

This will provide the results you want.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@jawalley 

Please consider changing your Formula to the following:

AddColumns(
    GroupBy(
        Filter(cardDeckSharepointList,Not(IsBlank(Suit))),
        "Suit",
        "_data"
    ),
    "Count", CountRows(_data)
)

This will provide the results you want.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

This is not working. I am still getting errors. 

jawalley_0-1638563267078.png

 

RandyHayes
Super User
Super User

@jawalley 

You are using a data table in your photo and none of those columns will correspond to anything.

The formula I provided you gives you the two columns you asked for in your post - Suit and Count.

I cannot tell from your photo if you are putting the formula I provided you into the Items property of the datatable...but that is where it goes.

Then click on the Edit Fields link in the properties and get rid of the columns you have shown and use Suit and Count.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jawalley
Regular Visitor

Thank you for your help @RandyHayes . Unfortunately it still is not working. I do have the formula in Items, and have hidden all columns but Suit and Count, using the Edit Fields link. 

If we can get some version of this to work, is there a less manual way to hide fields? I am using cardDeck as a sample, but the real data table I want this to work with has about fifty fields I would need to hide. 

 

jawalley_1-1638910317527.png

 

jawalley
Regular Visitor

I believe the issue had something to do with the "Suit" field being the ID or Title field, I was able to get the desired result with the following after setting a new Suit field. 

 

AddColumns(
GroupBy(Filter(cardDeckSharepointList,Not(IsBlank(Suit))),"Suit","_data"),
"Number of Cards",CountRows(_data)
)

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

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 (2,659)