Create Table counting rows of each type

(0) Share

Report

Posted on by jawalley

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

Suit	Number	Other	Info
Hearts	3	blah	blah
Hearts	4	blah	blah
Hearts	5	blah	blah
Spades	3	blah	blah
Spades	9	blah	blah
Diamonds	2	blah	blah

And I want my app to show

Suit	Count
Hearts	3
Spades	2
Diamonds	1

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (2)

Report
@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.

Was this reply helpful? Yes No
jawalley 36 on at

Like (0)

Report

This is not working. I am still getting errors.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
jawalley 36 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
jawalley 36 on at

Like (1)

Report

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)
)

Was this reply helpful? Yes No