Answered

Count Group By Table

(0) Share

Report

Posted on by ViTko

Hi,

I have a sharepoint list from which I need to create a table that looks like the one below. The problem is that both Category and Nominees are tables. Category is a multiple choice column on sharepoint so it is listed as table while Nominees are person column that can contain even multiple people for each category. What I need to do is a table that counts how many projects for each category (Unique or distinct) and then how many nominees for each Category. Problem I am encountering is that those are tables inside tables inside tables (nested tables). Thanks a lot for your help.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@ViTko

Can you explain more about how it is that you have a SharePoint list that has nested tables in it?

If that is not what you meant, then can you provide the formula you are using to group your data?

Was this reply helpful? Yes No
ViTko 8 on at

Like (0)

Report
Copy link

Link copied!

Well, I dont know if they are nested but I have problems to do a groupby on the columns that are choice ones on Sharepoint. Thanks a lot for your help. In this case Category is a choice and Nominee is a person so it contains a lot of information for each record such as email, displayname etc. Thanks a lot for your help.

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

Like (0)

Report
Copy link

Link copied!
@ViTko

So then consider the following to group your data:

AddColumns( GroupBy( AddColumns(yourListName, "_category", Category.Value ), "_category", "_records" ), "_projectCount", CountRows(_records) )

The above will give you the categories grouped and the counts for the projects.

As for the nominees, tell me more about that column - I know it is a person column as you have mentioned, but is it multiple-select or single-person?

Was this reply helpful? Yes No
ViTko 8 on at

Like (0)

Report
Copy link

Link copied!

Hi Randy,

This is exactly what I wanted, but I also need to add to it that Nominee column so it counts how many persons are for each project. Nominee column is a "Person or Group" column that allows for multiple selections.
Thanks a lot for helping me.

V.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@ViTko

Very good. Then your formula would be:

AddColumns( GroupBy( AddColumns(yourListName, "_category", Category.Value ), "_category", "_records" ), "_projectCount", CountRows(_records), "_nomineeCount", Sum(ForAll(_records, {Value: CountRows(Nominee)}), Value) )

Was this reply helpful? Yes No
ViTko 8 on at

Like (0)

Report
Copy link

Link copied!

Thanks a lot for your help Randy.

V.

Was this reply helpful? Yes No