cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Creating a Collection

I have a SharePoint List with 3 columns, a location, Date and Value column. I want to create a collection, with the same 3 columns, but only has one row for each distinct location and Date, with the sum of the values corresponding to that location and Date.

Here is an example:

in SharePoint:  Location            Date         Value

                       New York      12/23/2020       5

                       New York      12/23/2020       7

                       New York      12/25/2020       2

                            L.A            12/23/2020       3

                            L.A            12/23/2020       1

                            L.A            12/26/2020       10

 

Desired Collection Output:   Location            Date          Value

                                            New York       12/23/2020      12

                                            New York       12/25/2020       2

                                                  L.A            12/23/2020      4

                                                  L.A            12/26/2020     10

 

Is this possible, and if so how would I do it?

The goal is to have the collection be created either OnStart of the app, or OnVisible of the first screen.

Thanks

        

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi@mrizz,

Based on the issue that you mentioned, do you want to create a collection to store the grouped result?

Here is a workaround, please check as follows.

In my scenario, I have a SP like this:

61.png

Set the OnStart property of the App as below:

 

Collect(
    ColTest,
    ShowColumns(
        AddColumns(
            GroupBy(
                Test1224,
                "Location",
                "Date",
                "Combine"
            ),
            "SumValue",
            Sum(
                Combine,
                Value
            )
        ),
        "Location",
        "Date",
        "SumValue"
    )
)

 

 62.png

 

Regards,

Qi 

View solution in original post

3 REPLIES 3
Super User II
Super User II

Hi @mrizz 

Although it is not exactly what you are requesting, I think I would use the GroupBy() function to group on cities and show the data in a gallery with a subgallery.  Something like the screenshot below.  Here the GroupBy() is used on a contacts list to create the collection in the OnVisible property of the screen, as shown in the Header.  The Gallery Items property is "contactsbystate" and the subgallery Items property is "restofdata".  The company name and city are items in the subgallery.  With your data, you would put the Sum() function in the main gallery and sum the values in the subgallery._1.png

Community Support
Community Support

Hi@mrizz,

Based on the issue that you mentioned, do you want to create a collection to store the grouped result?

Here is a workaround, please check as follows.

In my scenario, I have a SP like this:

61.png

Set the OnStart property of the App as below:

 

Collect(
    ColTest,
    ShowColumns(
        AddColumns(
            GroupBy(
                Test1224,
                "Location",
                "Date",
                "Combine"
            ),
            "SumValue",
            Sum(
                Combine,
                Value
            )
        ),
        "Location",
        "Date",
        "SumValue"
    )
)

 

 62.png

 

Regards,

Qi 

View solution in original post

Hello, thank you this formatting works for me but i have run into an issue. There will only be a max of 1,850 rows in the collection, under the 2000 row limit i have set, but the sharepoint list it is reading through has 28,000 rows, and I think it is not reading through the whole list. Is there a work around for this? 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (29,330)