Group By in Power Apps Gallery with Large SharePoint Lists

hello everyone reza here in this video i will show you how to perform group by on data in powerapps galleries i will show two techniques one leveraging the group by function and the second technique is the one in which you can group by on large data sets supporting delegation in powerapps i will also showcase filtering and sorting of grouped by data so let's check this out in action [Music] to start exploring how to group data inside a gallery in powerapps i have a canvas power app that is connected to a sharepoint list my sharepoint list is a list of student information to group data in powerapps we have a function called group by and group by returns a table with records that are grouped together based on the column that you've specified to group the data the records in the same group are placed into a single record with a column added that holds a nested table of all the remaining columns so let's look at this in action in my power app i will go ahead and insert a blank flexible height gallery the items property for this gallery i would want the data in this gallery to be grouped based on the region that the student belongs to region in my sharepoint list is a choice column with the following four choices group by the name of my data source which is my sharepoint list of students then here i need to provide the name of the column on which i want to group my data on my region column is a choice column which is considered a complex type of column this will show simple columns like text dates the internal id column and more so what i need to do is i need to get that region choice column data which is a complex type column into a simple type column format and to do that i will use the function add columns my data source would be my students list the name of the column that i want to add i will call it region text and the expression to grab that value will get the value from my region column which is a complex type column it's a choice column so if i do region dot value it will give me the text value of that which now will be held in a column that would be added right here in this expression which would be region text now when i group by this data i can group by the region text column so i will pick that then i need to give the group name this is the column name to be used for the resulting nested grouped data that means after it does the grouping the table that has all the data related to that group what do you want to call that column and i will call it data and i will close my group by function the moment i complete my expression right here if i was to open this experience we can see there are two columns that are output by this group by function region text that is the column that i'm performing the group by on i have four regions not south east and west and i have another column called data which has tabular information so region south the data column will contain all the records that are related to the region south north all the records that are related to the region north and so and so forth now that i have my data grouped i can leverage this information to create the resulting experience in my gallery my gallery control i selected a flexible height gallery at the first level of this gallery i will add the headings basically the group headings which is my region text information and then for each of those gallery items i will insert a nested gallery to show all the data which is the tabular data with respect to those groupings i will click on this pencil icon to edit the gallery i will go and insert a button control so this creates all my headings right here the text property for this button i will select this item dot region text so it will give me all my group by values gallery has a property called is selected it will let you know which item in the gallery is selected based on the group selection that the user makes i would like to highlight that with a different visual indicator so for my button control i will head over to the fill property and use the following formula if this item dot is selected use my dark green color else i will go ahead and fade the color by 0.3 if i preview the app it's a flexible height gallery so we can see that it takes the real estate based on the controls within it right now south is selected we can see that with the visual indicator of that dark green if i select north the selection goes to north and so and so forth now i want to show the data that is held within each of those groupings once again i will edit my first item in the gallery and in here i will go and insert another gallery control and i will once again pick a flexible height gallery control this i will begin its position from right below this button so i've dragged this down the height of this new gallery i will set it as parent dot template height minus my button control which i have renamed to btn header dot height so it will calculate the height and fit that right inside that space now if i need more real estate to play with here can go to my main gallery go to edit and change the template size accordingly the nested gallery will expect a collection or an array of data and to provide that in the items property of this gallery i can leverage this item dot data data is the column that got created which has all the rows related to the grouping that i performed using the group by action now this is also a gallery control so i can edit this define the template size in here i can start adding the information that i would like to showcase for example i'll add a label control and this one would be this item dot and here i will get all the columns from my sharepoint list available for me right here so i can show the title which includes the name of the student i will copy this and add another control within this gallery here i will show the class the student belongs to class is a lookup type column so i'll pick class dot value so this will give me the class information for that student and i can add other pieces of metadata as well right here by adding additional controls now once i have completed this if i was to preview my app i can see the data based on the groupings for example the region is south these are all the students who belong to the south region if i scroll down i have north students belonging in the north region west and east however i would like to only show the nested gallery if the current item in the gallery is selected so to do that my nested gallery the visible property i can set it to this item dot is selected now if i preview the app it only shows me the student information for the south region if i pick north will give me the student information for the north region and so and so forth my main gallery i know that i have only four groupings so why not leverage the additional real estate that i have so i can span this across almost the entire height of the screen so the other three buttons fit in there and my nested gallery which has the scrolling experience i can turn this off and change this to a navigation like experience so if we preview the app these are all the students that belong to the south region if i pick north i have all the students here belonging to the north region taking the same steps but i have styled my gallery my nested gallery here is showcasing the student information in this card like format and here are my region based group by buttons now this group by function works very well for scenarios in which your data set is small in power apps we have a concept of delegation delegation is the ability of the power app to delegate the work of performing the query in our case group by data from our data source that operation powerapps would want to delegate to the data source that it is connecting to if that function against your data source is not delegable for example group by which is not a delegate function in that case powerapps will load only the first 500 records in memory and then perform the group by query on your data set my students list has over 5 000 records in my power app where i have these group headings defined on the bottom for the text property of this button i will also add the count of the number of items that are held inside this nested gallery so that we can see how many items this powerapps actually loading so the formula that i've added here is if this is the selected item in the gallery then go ahead and count the items that are being held in my nested gallery if i preview the app i have 151 students that belong to the south region if i click north i have 126 here i have 69 on the west region and i have 154 in the region east if you add up all of these numbers these numbers will amount to the number 500 that means my query is currently not delegable and the reason is because the group by function is not a delegate function with respect to any data source so if it is not supported powerapps can only work with 500 records but a scenario like mine i have a sharepoint list which has 5000 plus records and more or less in most of your use cases and scenarios you will have data spanning beyond 500. now one route that most people take is they quickly go to settings and right under general there's a setting for the data role limit which by default is 500 for an app and they change this to 2000 the reason is because 2000 is the maximum delegation limit that you can set changing this to 2000 will now start loading more data in memory and you can see the performance of my app degrading if you observe it took a while before the gallery loaded in this scenario and that is because powerapps is doing the work of loading 2000 records from my backend data source into memory so that it can then perform the group by action on it and present the results to me i'm running this on my browser experience i have decent bandwidth but what if i was to run the same app on my mobile device wherein my bandwidth is low in that case you can imagine how long it will take for this screen to load and even now if i was to calculate all these numbers for the south region north region west and east it will amount to only two thousand what if i have more data power apps cannot work with more than 2000 results no matter what you do if you are running into a delegation warning there are ways to work around this as well for example i've seen folks load the data into collections they create a collection load the first 2000 records then keep adding records to the collection but data in a collection where is that held it's held in memory in your power app once again you're loading data in memory it's going to slow down the performance of your app drastically there's also a distinct function which allows you to get the distinct values from your data source and then you can perform queries against your data source but once again distinct is not a delegable operation now the question is how do we get around this here what i will do is i will change my technique i'll take the same scenario i want to group by the regions region is a choice column in my data source for my main gallery control the items property i will change this to choices of my data source dot my column of choices which is region this will work for lookup columns as well when i do that it will return a column value which will have my four choices the text of my button control which is where my main headings are posted i will use the function this item dot value so i will get my four regions which is not south east and west now within this gallery i have my nested gallery the items for which was coming from this item.data data was coming based on group by now i do not have that anymore but here i can go and query my data directly from my data source and ensure that the query that i perform is delegable for example filter my data source which is my students list where the region column which is a choice column dot value is equal to this item dot value this item.value is those group headings now if i complete my function and if i was to preview my app you can see that when i pick north it queried the data for the north region if i go to south it will show me the data for the south region and so and so forth this is a delegal query against sharepoint as a data source why because filter is a delegable function and the operation equals is also delegable i've done a full series on delegation i'll paste the link in the description of this video so do check that out now here let's try and see the count of the number of items in the gallery so if this is the selected item then go ahead and count the rows in my nested gallery dot all items now the moment i do this and preview the app observe it says hundred the delegation limit that i set for my app i changed it to 2000 to prove my point that this query is delegable i will change this limit to one one is the least that you can set even though i changed the delegation limit to one it still shows me a hundred records this is delegation and action in powerapps when you make a query against your data source and the query is delegable that means the query is being performed at the data source level and powerapps is not doing the work that will make your app extremely performant since the work of performing the query will be done by the data source and once that query is performed the data source will return the results to power apps in an optimized manner and that optimization is by sending results back from the data source to power apps in batches of 100. what if the user wants to see the next set of data well to do that the user has to anyway scroll through the items in the gallery and go to that last record the moment you get to that hundredth record by scrolling down powerapps will load the next set of 100 records in memory in an optimized manner and we can see the count changing to 200 and this will keep loading all the data from your data source into powerapps as long as the user keeps scrolling and this is true no matter which group by region heading i select here's my gallery with 100 records based on students in the east region so this is the power of delegation and action you can see the load times of this screen is extremely fast and as i make the selections it quickly gets the data from my data source as power apps is not doing any of the work the work is being performed by the data source in this case sharepoint because the query is delegable and you can work with sharepoint lists as large as millions of records you can also apply filters to the data in your gallery for example here i have added a drop down control that has the different classes the students belong to to populate this drop down control i have once again leverage that same choices function on my class column which is a lookup column and in my gallery control wherein i had the filter for region dot value now i have added another filter that i have attached to it here i check to see is that drop down control blank that is if no selection is made then show me all the items or if a selection is made then query the class column dot value based on the drop down control dot selected dot value if i pick class 1 this will now show me all the students who are in the region north and belong to class 1 and there is not one student in my entire data set of 5000 records that has that scenario and that's a guarantee because this is a delegate query if i select class 3 i have only three students who belong to the region north and the class is class 3 and we can confirm that in sharepoint as well here are those three students in my entire data set if i go to class 2 i can see 100 records delegation once i scroll to the bottom it will load the next 100 records of students who belong to region north and are a part of class 2. and here i have even given a visual indicator to the user the user can see the number of records and whenever the records are returned in multiples of 100 i put a plus sign next to it just to add an indicator to the user that there could be more records and for them to see the records they would have to scroll to the bottom now it says 300 plus there could be more and the way i achieved that is by using the modulus operator here i use the with function to get the total number of items in the gallery store it in a variable called total the text that i showcase here first is my group by heading which is this item.value then i give the total count of the number of items in the gallery and then i use the mod function on that total count if modulus hundred because it loads data in batches of hundred if that is zero then you append this plus right next to it and we can see that in action here no matter which grouping i go to this technique you can expand to any group by logic of your choice here is a grouping of student data enrolled in the last five years for the items property of my main gallery i use the following function i'm using the sequence function the number of records i set five and the start value i set zero and i've run a for-all loop on this and here i get the current here we are in and i subtract this based on the value of the sequence so this gives me the output as follows we are in 2022 so 22 and the 4 years before that so i have the years listed out as my group by headings and for my nested gallery my formula is filter my students list where the enrolled date is greater than or equal to this item dot value will give me the group heading which is the year and i wanted the first day of the year so i've used the following function to grab that i check to see if the enroll date is greater than or equal to the date that the user has selected and less than or equal to one year from there so it gives me all the students who are enrolled in that specific year and to that i have also added some additional filters which i've provided right here on the top for region and class and this entire data set as well i can apply sorting as well here i'm sorting my students based on their names which is held in the title call and we can see all of this in action year 2019 i have the following students who have enrolled i can change the class to class 3 so this will show me only those students who i enrolled in class 3 in the year 2019 i can even filter this further by region show me all the students in the west region who were enrolled in 2019 and these are the results can change the year in this case i have no students who were enrolled in the year 2020 with the following filter combination if i remove my filters it will give me the data purely based on the group by which is enrolled here 2020 if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 81,375

Keywords: powerapps, power apps, powerapps groupby, powerapps grouping in gallery, powerapps nested gallery, powerapps group by gallery, power apps groupby, powerapps group by, powerapps tutorial, group by, powerapps groupby gallery, group by filter power apps, reza dorrani, group by delegation, how to, groupby function, powerapps groupby filter, powerapps group gallery items, powerapps groupby example, powerapps groupby count rows, powerapps group fields, powerapps group items, group

Id: 57ADxeo_13k

Length: 23min 36sec (1416 seconds)

Published: Mon Feb 21 2022