Count Group By Table

Like (0) Share

Report

Posted on 18 Feb 2022 15:29:18 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:

General Questions

All responses (6)

Answers (1)

ViTko 8 on 21 Feb 2022 at 14:42:55

Like (0)

Report

Re: Count Group By Table

Thanks a lot for your help Randy.

V.
Verified answer

RandyHayes 76,287 Super User 2024 Season 1 on 18 Feb 2022 at 17:15:50

Like (0)

Report
Re: Count Group By Table

@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) )
ViTko 8 on 18 Feb 2022 at 16:48:15

Like (0)

Report

Re: Count Group By Table

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.
RandyHayes 76,287 Super User 2024 Season 1 on 18 Feb 2022 at 15:46:01

Like (0)

Report
Re: Count Group By Table

@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?
ViTko 8 on 18 Feb 2022 at 15:39:09

Like (0)

Report

Re: Count Group By Table

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.
RandyHayes 76,287 Super User 2024 Season 1 on 18 Feb 2022 at 15:34:41

Like (0)

Report

Re: Count Group By Table

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