Answered

combining two collections

(0) Share

Report

Posted on by morall Microsoft Employee

ClearCollect(
 test1,
 Choices('HM InterviewStatus')
);

this turns an option set into a collection:

ClearCollect(
 test2,
 ForAll(
 test1,
 CountIf(hm_request_can_id, InterviewStatus = Value)
 )
);

this loops through that collection, and counts how many times each value shows up in hm_request_can_id

Is there a way to combine these two collections so that its 1 collection with 2 columns: status & count?

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@morall

Yes, but can you explain what hm_request_can_id is? Is that a datasource?

If so, then this formula would give you what you need:

AddColumns( RenameColumns(Choices('HM InterviewStatus'), "Value", "status"), "count", CountRows(Filter(hm_request_can_id, InterviewStatus = status)) )

I hope this is helpful for you.

Was this reply helpful? Yes No
morall Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes it is a collection.

ClearCollect( hm_request_can_id, Sort( AddColumns( Filter( DataSource Candidate.ID in tessa_candidates, 'Created On' > DateAdd( Now(), -90, Days ) ), "custom_candidate_id", Candidate.ID ), 'Created On', Descending ) );

Was this reply helpful? Yes No
morall Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes

I'm getting an empty collection:

ClearCollect( test3, AddColumns( RenameColumns(Choices('HM InterviewStatus'), "Value", "status"), "count", CountRows(Filter(hm_request_can_id, InterviewStatus=Status)) ) );

I feel like I'm not doing the AddColumns(), the first parameter is expecting a datasource and I'm asking it to rename a column....

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@morall

RenameColumns is a datasource. It returns a table of the Choices function.

Oh sorry though, I didn't even notice - you are specifying a Datasource for Choices but no column to get choices from. What Column do you want the Choices from? You need to specify that.

Was this reply helpful? Yes No
morall Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes

Choices('HM Interested')
is an option set

I'm trying to create a collection that has two columns:
Column 1:
Choices from option Set

Column 2:
Count how many times that option appears in the other data source

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@morall

Yes, I understood that.

The formula I provided should give you what you want. Since it is not, let's explore a little.

You originally had a formula of:

ClearCollect( test1, Choices('HM InterviewStatus') );

When you had that, did test1 have values? And specifically, was test1 a table with a single column called "Value"?

I had assumed Value based on your second formula.

Was this reply helpful? Yes No
morall Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Randy

Yes, your intuition is correct. it returned a collection with 1 column that had the option set value. I can't quite figure out how to add a column to that 🙂

Was this reply helpful? Yes No
morall Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
ClearCollect( test3, AddColumns( RenameColumns(Choices('HM InterviewStatus'), "Value", "status"), "count", CountRows(Filter(hm_request_can_id, InterviewStatus=Status)) ) );

this is what I see:

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@morall

Perfect - thank you for the details.

So, let's start basic and build up.

ClearCollect( test3, AddColumns( RenameColumns(Choices('HM InterviewStatus'), "Value", "status"), "count", 1) ) );

Using the above formula, you should have a two column collection called test3 that should have 2 columns - "status" and "count" each row will have a count of 1 with this formula.

I do see a typo in my original formula, so try this then instead on the "full speed ahead" formula:

ClearCollect( test3, AddColumns( RenameColumns(Choices('HM InterviewStatus'), "Value", "status"), "count", CountRows(Filter(hm_request_can_id, InterviewStatus=status)) ) );

It was that I simply had Status instead of status.

So, let's see what you get with formula 1 and then the second formula.

Was this reply helpful? Yes No
morall Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

once again, you were spot on. the "1" worked and the updated formula worked.

Thank you, this helped me understand the mechanics of working with collections!

Was this reply helpful? Yes No