Aggregation nested collection

[Sander0123]

Hi, 

I'm trugeling with a function for a few hours now and I'm hoping someone out there can help me out.

I've a collection ORDERS with a nested table LINE_ITEMS that contains a field NAME (product name) and a field QUANTITY. My goal is to sumarise the the quantities per unique product name in a collection.

Thanks!

[h-nagao · Accepted solution]

Nested collection can be expanded by Ungroup function, so you can get summary table (collection) containing unique product name and total quantity by

ForAll(Distinct(Ungroup(ORDERS,"LINE_ITEMS"),NAME),Collect(SummaryList,{ProductName:Result , TotalQuantity: Sum(Filter(Ungroup(ORDERS,"LINE_ITEMS"),NAME=Result),QUANTITY)}))

Just in case, below is screenshot of collections in my test app:

[Sander0123]

Thank you very much. It work like a charm. Good start of this new year. All the best for this year to you and thanks again for your efforts.