How to create a collection that counts the common values

(0) Share

Report

Posted on by EmilioRoqueta69

565

Hi,

I need to summarize the people that work in the same Department, in a dataverse table.

So, by example, I have this data in a dataverse table and I need to get the result table/collection.

I think that it can be solved by a collection right?

Because there is no way to create a SQL view inside the dataverse.... or yes ?

Thanks in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

v-mengmli-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @EmilioRoqueta69 ,

If there is no specific column, please try this.

ClearCollect(COL,RenameColumns(AddColumns(Distinct(DataSource,Department),"Total",CountRows(Filter(DataSource,Department=Value))),"Value","Department"))

Best regards,

Rimmon

Was this reply helpful? Yes No
EmilioRoqueta69 565 on at

Like (0)

Report
Copy link

Link copied!

Hi Rimmon,

I replaced your formula with real values and I have this error message.

In other hand, I put this formula and it has no error, but it does not count all the rows that I have.

ClearCollect(
MyCollection,
DropColumns(
AddColumns(
GroupBy(Filter(Inventory_ManagedDevices, !IsBlank(cr815_department)),"cr815_department","Total"),
"Count",CountRows(Total)
),
"Total"
)
);

Thanks for any help!

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @EmilioRoqueta69 ,

Please try.

Or

ClearCollect( MyCollection, ShowColumns( AddColumns( GroupBy(Filter(Inventory_ManagedDevices, !IsBlank(cr815_department)),"cr815_department","User"), "Total",CountRows(User)),"cr815_department","Total") );

Best regards,

Rimmon

Was this reply helpful? Yes No
EmilioRoqueta69 565 on at

Like (0)

Report
Copy link

Link copied!

Hi Rimmon,

Thanks for the help.
Something is not working with the Filter.

This formula using Filter, counts more rows than the second one, BUT there are more records to count:

ClearCollect(
MyCollection,
ShowColumns( AddColumns(
GroupBy(Filter(Inventory_ManagedDevices, !IsBlank(cr815_department)),"cr815_department","User"),
"Total",CountRows(User)),"cr815_department","Total"));

ClearCollect(
MyCollection,
ShowColumns( AddColumns(
GroupBy(Inventory_ManagedDevices,"cr815_department","User"),
"Total",CountRows(User)),"cr815_department","Total"));

My dataverse table has 3000 rows.
I did try to put a dataverse view (that filter the empty data) inside the formula, but it makes an error message.

Was this reply helpful? Yes No
Verified answer

EmilioRoqueta69 565 on at

Like (0)

Report
Copy link

Link copied!

This formula works !

ClearCollect(MyCollection,RenameColumns(AddColumns(Distinct(Filter(Inventory_ManagedDevices, !IsBlank(cr815_department)),cr815_department),"Total",CountRows(Filter(Inventory_ManagedDevices,cr815_department=Value))),"Value","cr815_department"))

Was this reply helpful? Yes No