Avoid 2000 records issue in Power Apps with Out of the box functions

hi everyone this is krishnava napo a powerapp super user welcome to my channel we are continuing our powerapps functions zero to hero series and this is part two in this video i would like to cover the bottleneck issue of 2000 records processing we can avoid this issue with five out of the box functions they are group by start with clear collect or collect add columns and sum let us see each and every function how we are going to use and how we can avoid this 2000 records issue the business case for this video is if the data source exceed 2000 acre can not perform group by because it will only consider maximum of 2000 records we know that powerapp has a limitation of max 2000 records reading and these are the out of the box five functions i am going to use to avoid the issue before we dive into the demo let me take two minutes of your time to see each and every function and its syntax as per microsoft first function i wanted to explain or to demonstrate is group by the group by function is going to always consider which column user want to group by and it will create group of that column and always gives another table and if you see the syntax group by which is the data source of the table and column name on which column you want to group by and what is the name of the group you want to because when we say group by what happens is when we say group by city population country and cities is the group name the example what they were trying to explain here when we see the output of this it is grouping with the country but cities will always follow with the table because the data source has city country and population it will group by country and city and population will come as a table for each and every country so when we are using group by we should always provide like group by what is my data source and what is the column i want to group on and what is my group name and next one is start with start with always works against a string and start with looks for what is my input string and what is you want me to start with search and when we see the syntax of this is start with the string and followed by a character or string and how does that work is in this example start with always return a boolean value and i'm trying to say start with hello world in this he is we are trying to say hello is hello existed in start with the beauty we start with is it is case insensitive i need not worry whether i have mentioned h as capital or small and the next function is clear collect and collect clear collect is to create a collection by clearing all the data in that collection if the collection is already created and collect is to add the data to the existing collection and last but not least the sum function some function adds the number of integer values as part of the provided data source if we see the syntax of the sum function is some data source or the table name and a numerical formula which is here it expects some data source and followed by whichever the column you want to do the sum to demonstrate this functionality i have created a list in sharepoint with name as student information and i have populated 15 000 records in that and the column structure or the stable structure of this sharepoint list is title state nces lea id local education agency number of valid test takers my user want to see the number of valid test takers for each and every agency in a gallery i repeat the user requirement is user want to see how many test valley test takers for each and every agency let me go on to powerapps now i want to ensure that my item limit for the powerapp is as minimal act as i want i have given 100 by default when we create a app the minimal value is 500 but intentionally i have provided the value as 100. now i go back and let me add my sharepoint list as the data source now first function we want to use is user want to see at local agency level so let me add group by student information followed by local education agency and as for the syntax data source followed by which column we want to group by followed by group name my group name is table agency now this will gives me the group of all the agencies which are under 100 records because if we see the item the data source limit we have provided at the app level is 100 so it can maximum consider 100 records while it is grouping okay let me add add columns to see to some add columns followed by valid test acres followed by now sum i want to use this table because we know that it is going to give a table in that table which column i want to add is number of valid test takers and if i see that each and every agency i got valid test takers total values and if we validate against my data source let me go to my data source and veganist each and every value let's see how many records we have for this in this density i should get 334 as number of valid test takers if i go back to my power app and see in the density i got 328 as the total value but ideally i should get 334 the reason behind is it cannot consider 101 because my app limit is only 100 records the similar way if i provide 2000 it can consider only 2000 records but what about my rest of the data which is very bothering factor for all the app makers as per our objective what are the five functions we wanted to use one is add columns group by sum we have already used three functions what else is left is start with let's let me add start with to see how we can enhance our group by to extend my gallery with additional values let me add a button on button on select what i wanted to do is i want to add the cleo collect i wanted to create a collection here collection students followed by the entire formula whatever the formula i have used here copy this but here instead of grouping again as the students i want to change a little bit here what i want to do is i want to filter this data source in such a way that filter my data source start with which column i want to start i want to search against is local agency and start with a and when i format the formula it will come like this what i have done is i have added start with with this column and starting with the letter a so what i'm trying to do now here is i don't want to group all the local education agency first i want to group all the agencies with the starting letter starting value as a and when i just execute this formula and look at this collection now understand city earlier we have 328 now we got 334 which is the accurate number with 101 value also added when we extend this formula again again one more time i want to see the existing filter existing filter is coming with only 328 which we have applied to the gallery but whereas in this battle i have applied with start with now i got an intensity with 334 and moreover if we see we got more values which are relevant to the local integration agency where the name starts with a now if we want to extend the same functionality for all the rest of the list for the entire list what we can do is we can add the same formula and just say instead of clear collect i will say collect and i will replace this a with b and when i execute this code now my collection has a value starting with a as well as b this way i can continue for all the 26 alphabets now i have extended my code in such a way that it should consider each and every alphabet e a b c d until the last character z now when i click on that button it will go and read each and every letter starting with that local agency group and it will create my collection with all those agency names and some of each and every valid test taker values there you go i got my entire list process where my list is 15 000 records and when i bind if i bind that collection students to my gallery after i do a bit of formatting on my gallery the entire list will look like as this and when i play this i can see my gallery has more than 100 records let us see that by inserting a label count rows gallery four dot all items there it is my total number of entries in my gallery is 268 entries in this way we can absolutely avoid the problem of group by when my data source is more than 2000 records just want to give one more time a recap on the formula in this formula we are using clear collect or collect add columns second function third function is group by and fourth function is filter and fifth function is start with sum is additional sixth function maybe filter we can ignore because we know filter very well so these are the total five formulas out of the box formulas we can use without involving any other flow or any other separate list to split the data to perform group by if you like this video do like comment share and subscribe to my youtube channel for all future video notifications here is my contact i am highly available on twitter and linkedin i can respond immediately on twitter thank you for your time have a nice day

Channel: Krishna Vandanapu - MVP

Views: 2,679

Rating: 4.9285712 out of 5

Keywords: Avoid 2000 records issue in Power Apps, PowerApps, Power Apps, GroupBy, StartsWith, functions, 2000 record limit, larger datasets, PowerApps large data, Avoid 2000 records issue in pwoer apps, powerapps tutorial, powerapps sharepoint, power apps dynamics 365, power apps ideas, power apps demo, powerapps patch examples, powerapps sharepoint list, powerapps 500 row limit, powerapps 500 record limit, Powerapps tutorial 2020

Id: Zvl1K42SCyQ

Length: 15min 5sec (905 seconds)

Published: Sun Oct 11 2020