cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kman42
Level: Powered On

Adding a column with grouped results

I have two tables, People and Projects. I would like to create a collection called colPeople that includes all of the columns from People, a column with a table of Projects grouped by the unique key person_code, and a column counting the rows in that grouped column. This is what I have so far:

 

ClearCollect(colPeople,AddColumns('People',"PeopleProjects",GroupBy('Projects',"person_code","Projects")))

This kind of works. It creates a collection with all of the columns from People and adds a grouped column, but the grouped column contains two columns, one with the rows I want and one with "person_code". How do I just get the rows without the additional column? Here's what it currently looks like:

 

colPeople

   person_code

   name

   title

   PeopleProjects

      person_code

      Projects

         [all of the columnns for Projects]

 

Basically, I've created an extra layer in the hierarchy that I don't want (PeopleProjects). I have no idea how to also include a column that counts the rows in Projects. This is the structure I am looking for:

 

colPeople

   person_code

   name

   title

   Projects

      [all of the columnns for Projects]

   countRowsInProjects

 

I feel like I am super close to getting this and once I do, I'll have a pretty decent understanding of how to structure the data to get what I want out of PowerApps.

 

Thanks!

 

 

      

2 REPLIES 2
Super User
Super User

Re: Adding a column with grouped results

@kman42 

I'm not sure that in this case the GroupBy function is what you would want.  Based on your formula, the "PeopleProjects" column will contain a table of two columns - the distinct "person_code" column and another column "Projects" that will contain all the project associated with that distinct person_code. And, this will be for all rows in the Projects source.

 

I believe what you want is the "PeopleProjects" column to contain a table of all the Projects that match that person_code.  Additionally, you want another column that contains a count of those projects.  If that's the case, then you will need an inner and outer AddColumns - one to get the projects, and another to count them.

 

So, I believe this formula is more in line with what you might be looking for:

ClearCollect(
colPeople,
AddColumns(
AddColumns(People,
"PeopleProjects", Filter(Projects, person_code=People[@person_code])
),
"projectCount", CountRows(PeopleProjects)
)
)

Notice that we (on the inner) get the rows from Projects that match the person_code.  Our row in the collection will then have all of those rows for just that code.  Then (on the outer) we add another column with the count of those inner rows.

 

I hope this is clear and helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
TopShelf-MSFT
Level 10

Re: Adding a column with grouped results

Hi @kman42 how are you progressing with this? Was the above reply helpful? 

 

@TopShelf-MSFT 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,553)