Answered

Add Columns to collection

(0) Share

Report

Posted on by MikeBeman

Good morning,

I am struggling to get the formula correct to add a column to a collection. I want to use the collection as the filter for a drop down elsewhere in the App, when the count of the item = 3, don't show the item. I can get this to work in a gallery, but get a delegation warning.

I have 2 SharePoint lists that I am using, 'Tooling List' and 'Tooling Booking List'. I have created 2 collections for those on the App OnStart.

colToolingSets = ClearCollect(colToolingSets, 'Tooling Sets');

and

colBookedOut = ClearCollect(colBookedOut, Filter('Tooling Booking List', OutIn = "Out"));

I want to add a column to colToolingSetsWithCount that counts the number of rows in colBookedOut where the Title values for each match.

e.g. Tooling set 100g Set 1 is listed 3 times in colBookedOut

However, When I use the below code I get 12 for every record which is the total number of rows in the collection, I should only be getting a count of 3 for any Tooling Set. and based on current data, only 4 of the sets have that figure (hence 12, 3 x 4), the other 11 have 0 items booked out.

I have used the below to get that outcome, what am I missing..

ClearCollect(colToolingSetsWithCount, AddColumns(colToolingSets, "BookedOutCount", CountIf(colBookedOut,Title = Title)));

Thanks

Mike

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

v-mengmli-msft on at

Like (0)

Report
Hi @MikeBeman ,

Please try this.

ClearCollect(colToolingSetsWithCount, AddColumns(colToolingSets, "BookedOutCount", LookUp(AddColumns(GroupBy(colBookedOut,"Title","test"),"Count",CountRows(test)),Title=Title,Count)))

Best regards,

Rimmon

Was this reply helpful? Yes No
MikeBeman 28 on at

Like (0)

Report

Hi Rimmon,
Thanks, this is better, but now I get 3 as the result for all items

Was this reply helpful? Yes No
Verified answer

BhaskarDhone 1,392 Super User 2025 Season 2 on at

Like (0)

Report
your code looks correct only thing is you have common field name hence system is getting confused.
Try this.

ClearCollect(colToolingSetsWithCount, AddColumns(colToolingSets As Table, "BookedOutCount", CountIf(colBookedOut,Title = Table.Title)));

Was this reply helpful? Yes No
MikeBeman 28 on at

Like (0)

Report

Values I am expecting to seeWhat is in the collection

Was this reply helpful? Yes No
MikeBeman 28 on at

Like (0)

Report

@BhaskarDhone - this worked, thank you!

Was this reply helpful? Yes No