cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimmyNeutron
Helper I
Helper I

Using a list to count rows for each unique name entry

So I have a table with many entries. The data has many records with the same names. So for example, I have 7 different entries that have "Fire" under name, 6 entries with "Water" under name, and 3 entries with "Grass". This would be in Table1. The data is taken from Sharepoint. The data would look something like this (with other columns)

Fire

Fire

Fire

Fire

Fire

Fire

Fire

Water

Water

Water

Water

Water

Water

Grass

Grass

Grass

 

How can I create a new Table2 with a new column that just takes all of the unique values in the name column (in this case, Fire, Water, and Grass) and then count how many entries of each unique record exist? So in this example, the final table would look like this

Fire   7

Water    6

Grass    3

 

Ideally, I'd want to avoid hard-coding, so that if someone adds 2 "Earth" entries for example, the app can add a new entry in the final table. 

 

I'm thinking of using Filter and CountRows, but I'm just not sure how to go about counting different names in a single command on a table. 

 

Or would this be easier on a gallery? 

1 ACCEPTED SOLUTION

Accepted Solutions
CNT
Community Champion
Community Champion

@JimmyNeutron The CountryCount collection will have a column called Result that will have the country name and the count.

ClearCollect(CountryCount,AddColumns(
   GroupBy(
      CityPopulations,
      "Country",
      "CountryGroup"
   ),
   "Result",
   Country & " " & CountRows(CountryGroup)
))

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

View solution in original post

5 REPLIES 5
CNT
Community Champion
Community Champion

@JimmyNeutron Try this (replace SPList and column name as required),

AddColumns(
   GroupBy(
      yourSPList,
      "yourNameColumn",
      "Data"
   ),
   "NameCount",
   CountRows(Data)
);

 

 

Hmm, I'm trying to do this first with a simple example, but I'm not sure what to put for "Data". I keep getting an error for some reason and it won't display my table. 

 

Let's use this example to be more clear. I set the following to a button I press so the data is stored in "CityPopulations". In this example, I want to count how many times each country appears

 

ClearCollect( CityPopulations,
{ City: "London", Country: "United Kingdom", Population: 8615000},
{ City: "Berlin", Country: "Germany", Population: 3562000},
{ City: "Madrid", Country: "Spain", Population: 3165000},
{ City: "Rome", Country: "Italy", Population: 2874000},
{ City: "Paris", Country: "France", Population: 2273000},
{ City: "Hamburg", Country: "Germany", Population: 1760000},
{ City: "Barcelona", Country: "Spain", Population: 1602000},
{ City: "Munich", Country: "Germany", Population: 1494000},
{ City: "Milan", Country: "Italy", Population: 1344000}

 

Ideally, the final list would look something like this (With "Country" and "Count" columns)

United Kingdom 1

Germany 3

France 1

Italy 2

Spain 2

CNT
Community Champion
Community Champion

@JimmyNeutron The CountryCount collection will have a column called Result that will have the country name and the count.

ClearCollect(CountryCount,AddColumns(
   GroupBy(
      CityPopulations,
      "Country",
      "CountryGroup"
   ),
   "Result",
   Country & " " & CountRows(CountryGroup)
))

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

View solution in original post

@CNT 

Awesome thanks! I also wanted to ask this since I think it will be useful in the future. So I applied this to the button and I tried to display all three columns on a table. However, I get an error for the CountryGroup Column and only see [object Object]. How can I display items or get rid of this error? I've attached a screenshot below

CNT
Community Champion
Community Champion

@JimmyNeutron CountryGoup is a table. If you want to dig in you have to refer to individual fields within just as you would any other table. CountryGroup.Country or CountryGroup .Population

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,701)