Unanswered

Using a list to count rows for each unique name entry

(0) Share

Report

Posted on by JimmyNeutron

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

Water

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

CNT 10,921 Super User 2024 Season 1 on at

Like (3)

Report
@JimmyNeutron Try this (replace SPList and column name as required),
AddColumns( GroupBy( yourSPList, "yourNameColumn", "Data" ), "NameCount", CountRows(Data) );

Was this reply helpful? Yes No
JimmyNeutron 89 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

CNT 10,921 Super User 2024 Season 1 on at

Like (1)

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

Was this reply helpful? Yes No
JimmyNeutron 89 on at

Like (0)

Report

@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

Screenshot 2021-0...

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report

@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

Was this reply helpful? Yes No