Creating Complicated collection for gallery

[nmoffatt]

Hi,

I want to collect condensed information from a Sharepoint List. The list looks like this:

I want the results of the collection to look like this:

Detail of the columbs:

Order#- Distinct values of all Order#s

Total Quantity- Add Quantities together of like Order#

Status- Distinct value related to the Order#'s distinct value

JobNumber- Distinct value related to the Order#'s distinct value

JobName- Distinct value related to the Order#'s distinct value

[RandyHayes · Accepted solution]

@nmoffatt 

Consider the following formula:

DropColumns(
    AddColumns(
        GroupBy(yourList,
            "Order#", "items"
        ),
        "TotalQuantity", Sum(items, Quantity),
        "Status", First(items, Status),
        "JobNumber", First(items.JobNumber),
        "JobName", First(items.JobName)
    ),
    "items"
)

That should give you what you need.

I hope this is helpful for you.

[VijayTailor]

Hi @nmoffatt ,

You can follow the below approach also.

See the Master Data Is Used - 

Created the Collection - 

ClearCollect(MasterColl,Table1_2);
ClearCollect(GroupByColl,AddColumns(GroupBy(MasterColl,"Status","Order","JobNumber","JobName","ByStatus"),"TotalQuantity",CountRows(ByStatus)));

See the Result - 

Thanks,

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[nmoffatt]

@RandyHayes 

Worked perfect! Thanks