cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Group By Month and count the number of items per month

I have a SP list where is stored a security table
with info like
Date, Name, Employee Number, Grade of Risk, Type of Risk etc

The problem is that the list is over 2000 records

How can I group the items by Month (Date) and the type of risk

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Group By Month and count the number of items per month

@EC_1 

 

You can store the information as a group by using the GroupBy function.

Using this you can group the information onto a specific column.

 

Please check the documentation for more insights regarding the GroupBy function.

 

Also, you can check out the video by one of our MVP's regarding the same.

 

Highlighted
Community Support
Community Support

Re: Group By Month and count the number of items per month

Hi @EC_1 :

Because Groupby is not a delegable function, once the number of records exceeds 2000, then an error will definitely occur.

In addition,The date field is also not delegateable, you also need a separate field "month".

I have an alternative for your reference:

I assume you have added a number field "Month" into the SP list:

 

 

ForAll(
    Table(
        {themonth: 1},
        {themonth: 2},
        {themonth: 3},
        {themonth: 4},
        {themonth: 5},
        {themonth: 6},
        {themonth: 7},
        {themonth: 8},
        {themonth: 9},
        {themonth: 10},
        {themonth: 11},
        {themonth: 12}
    ),
    ForAll(
        Table(
            {therisk: "Risk1"},
            {therisk: "Risk2"},
            {therisk: "Risk3"}
        ), 
        Collect(
            thecollection, /*thecollection is my custom collection*/
            {
                Month: themonth,
                Risk: therisk,
                group: Filter(
                    YourSPlist,
                    Month = themonth,
                    Risk=therisk                    
                )
            }
        )
    )
)

 

 

I created a table using hard coding, and then traversed the table to create a new collection

Best Regards,

Bof

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,467)