PowerApps GroupBy function and Nested Galleries

[@SushGS2]

Loved it, Yesterday only I was struggling with the groupname parameter of this function, you cleared up the things so well. Exactly what i needed for my billing app . Thanks shane.

[@ShanesCows]

Glad it helped!

[@sarahjarman5781]

You literally are my PowerApps hero! Every time I'm stuck one of your videos has the answer! Thank you!

[@ShanesCows]

Glad to help Sarah 🐶

[@libbyspaur605]

Super timely, I just started a new project that needed easy ways to view child tables. THANK YOU!! :)

[@ShanesCows]

I got your back. 🤩

[@juanspottorno5615]

Great video Shane! and great function Group By! I belive it will work better with flexible size galleries

[@ShanesCows]

I agree. Good news, I have a video on those finally. kzbin.info/www/bejne/d6eqg5-oqrKJpKs 🙂

[@patrikgajdos468]

Brilliant! Thank you Shane. A very clear and to the point video as always. :)

[@ShanesCows]

Much appreciated!

[@Alan-wt1vo]

Great video on group by which is so tricky. Like the strategy of using DATA name.

[@ShanesCows]

Glad it was helpful!

[@sconsalter]

I was looking for a function like this in a while. Thanks Shane, once more.

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@LNG87PA]

Thank you, Shane. Everytime I find what I am looking for in your video or in the comments!!! :D

[@ShanesCows]

Love it!

[@emadaljundi7400]

Thanks a lot for your channel and lessons which improve my skills in Power Apps

[@ShanesCows]

It's my pleasure

[@jenskemeijs]

Dear mr. Young, again an awesome video! Thanks! But I was wondering, is it possible to put in a input text in which you can search/filter the nested gallery? So for example around minute 8 you had the names in the nested gallery. Is it then possible to put in a input box to search on "Chewy" and that automatically the "main" gallery is filtered so that only IT is showing, because in that department Chewy is working. (I'm very sorry for not being so clear with my question, but I do hope you understand what I ask)

[@katielux357]

@Jenske Meijs I was able to do this by adding the filter to the parent gallery. In my example they first choose what column to filter by (Radio2_3) then they select an item from their correlating dropdown. Columns "Category/Title" and "Definition" have repeating data so they are outside on my "Grouped" and "Consultants" is unique so its inside "DATA" Code for PARENT GALLERY: ITEMS SortByColumns( GroupBy( Switch( Radio2_3.Selected.Value, "Category", Filter(DataSource, Title = Category_dd.Selected.Result), "Definition", Filter(DataSource, Definition =Definition_dd.Selected.Result), "Consultant", Filter(DataSource, Consultants =Consultants_dd.Selected.Result)), "Title", "Definition", "DATA"), "Title", Ascending)

[@DaveMelton]

This is great. But I'd like to see something like a questionnaire with subheadings.

[@itdept1147]

Not exactly GroupBY, but pertaining to nested galleries, is there a way to Sum fields in nested galleries? Using your example above, if instead of First Name, you had Quantity, could you add the values in separate departments?

[@dankirchner4460]

Great video Shane!!!

[@ShanesCows]

Thanks 😊

[@traceyblackmore852]

Another great video! Thanks Shane, very beneficial.

[@AmazingHandy]

Love the videos. Helped me so much with a project for work.

[@ShanesCows]

Great to hear!

[@jaddfigaro7246]

Hi Shane, I watch most of your videos but im still soooo lost with PowerApps. Feels like it would be easier if it was like Power Query or SQL (now you have my background) ... anyway My question is, How can a access the "DATA" from the Groupby function in another screen ? Thank you for your help :D

[@DukeSr87]

Just helped me with a GroupBy I was struggling with for a client.

[@ShanesCows]

Awesome! 😎

[@zeedu1]

You Sir, are a life saver! Thank you very much.

[@ShanesCows]

You are most welcome

[@Gerberd4936]

I am trying to put a series of values from a lookup data type column using your concat technique. It is erroring saying the lookup is not text. Do you have a workaround for this? Thanks.

[@ShanesCows]

What is your formula? (Leave a new comment, not a reply, easier for me to find 🤩)

[@rodrigopresencebagarolo2892]

That's exactly what I needed, thank you!

[@ShanesCows]

You're welcome!

[@danielavasques5952]

Shane, first of all, thank you for one more incredible video! I have a question that you may be able to help me. I have a gallery in power apps and I have a filter in the items property of the gallery in order to filter the gallery by the current user (to only show the relevant information for them). However, I’d also like to add a GroupBy because I want to display the info based on a lookup column that I have. Both formulas work are working perfectly separately, but when I try do combine the filter with the group by to have everything in the items properly simultaneously, I always get errors. What can I do? 🙁

[@ShanesCows]

GroupBy(Filter(DataSource, User = User()), "ColumnToGroupOn", "RestOfYourData") That is roughly how it should be structured. This video on Nesting might help. kzbin.info/www/bejne/n3ibhJ2Nqb1kr68

[@danielavasques5952]

Thank you so much Shane!! I was able to make it work. ☺️

[@nicholascarlson2304]

Shane, I am stumped. I am not great at developing apps I am the first to admit that, but I love trying anyways. Essentially working on Data that is Job Postings and the candidates in the job postings Grouping by "Posting Title" then displaying the candidates name in the second gallery. I want to be able to click on those candidates and edit them in a form, but when I do and submit that form it takes the "Posting Title" out of their line because that data isn't transferred to the Form. Not sure what to try next.

[@ShanesCows]

You have the ID? YOu could lookup the Posting Title based on the ID and get it back that way.

[@nicholascarlson2304]

@@ShanesCows I do have the ID, nut sure how to get the posting title using that.

[@ShanesCows]

@@nicholascarlson2304 YOu have to use the LookUp function kzbin.info/www/bejne/jWa9Z2SHbreYntU

[@petereriksson6859]

So nice Rock on and keep up the good things .

[@ShanesCows]

Thanks 😊

[@Mx81]

Great. Never thought about using it. Maybe I’ll change my mind. ;)

[@ShanesCows]

I don't use it handy but it can be handy.

[@ilikeurstyle2594]

Are you able to make a guide for Power Apps version for multiple approvers and do an final approval after all the approvers approve it , by using SharePoint as the back-end-data. Great videos!

[@ShanesCows]

I talk about that here. kzbin.info/www/bejne/amqyY2tqmcqCd6s

[@stwilhite1]

Okay, I’ve needed this for like a year. You’re going to make me look like a hero, Shane, Executive.

[@ShanesCows]

Awesome Scott!

[@briancollier3303]

This is very useful, love the enthusiasm and humor. Any on getting that next arrow (was removed at one point) to lead to specific table rows associated with that corresponding item, probably in a new screen/gallery? Ex: clicking on "Finance" arrow leads to window with specific table data associated with "Greg", clicking "Executive" arrow leads to table data associated with "Nicole, Shane, etc."

[@moyura2]

GroupBy is a Non-delegable function, it only returns the first 500 records from the sharepoint list.

[@ShanesCows]

Correct

[@BhupendraSingh-nd4nb]

Great Video, you are a life saver 🌻

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@frog5799]

If not all rows in your table have a specifiied value for the column you want to group by (aka some rows are blank for that column), will this produce an error in PowerApps when trying to use the GroupBy function?

[@ShanesCows]

I have never tried but I assume it will just group all of the blank ones together also. Power Apps usually treats Blank as a value for things like this but I am not positive.

[@nilokillian]

and once you want to have a dynamic height for a nested gallery with some html stuff going on, everything turns into a mass with clashing components, especially when you have 100+ records in

[@jankutilek3954]

Thank you.....you are the BEST !!!!!

[@ShanesCows]

Thanks Jan!

[@loisyyymarie952]

hi Shane, is it also possible to create groupby with filter. For example "Groupby(Employees,"Department","DATA") but Department should not be equal for example to "IT"

[@ShanesCows]

GroupBy(Filter(datasource, logic), column, “DATA”) would work.

[@andreedmunds]

Hi Shane ... awesome and so simple. I have a further challenge ... where at the 10:00 mark you do a second grouping by LastName ... hwat if that last name is a lookup field in a different table ... in my example ... I have a many timesheets entered for a week ending where many resources (in a lookup table) are adding time against many projects. I want to group them by Week Ending and then by Resource Name (which is the lookup in another table). How do I achieve this in the nested gallery. Cheers Andre

[@ShanesCows]

You could use AddColumns to pull in the lookup field then group on it. That is my first thought on what I would try. PowerApps AddColumns ShowColumns kzbin.info/www/bejne/faXdpGRjftqLq7s

[@andreedmunds]

@@ShanesCows Brilliant Thx. Took me a bit of jiggery pokery to get it sorted to get my head around the multiple layers of tables then data within, then data within and how to access it to do stuff like total days within a resource within a week ending. Now I've just gotta work out how to get the Approval button to apply to all Status's within the Resource within the week. LOL ... DOH!!

[@lawrenceparker5045]

Great tutorial! I cannot get this to work for a dataverse table I created. I dont get the " " for the named fields; I'm provided the prefix for my custom tables (ie. dr490_costcenter and not "Cost Center"). GroupBy(Requests, dr490_costcenter, "Cost Center") gives me the "Expected identifier name" for the group name "Cost Center".

[@iansuffield351]

Hi Shane, I am using the Groupby function and it works; my data set has text column in SP doc library in the format "xxx-yyy-zzz"; do you know of a way to group by the "xxx-yyy" section and combine all of the "zzz" into the one set, rather than the whole text value in this column? (I hope this makes sense!!)

[@sarahjarman5781]

Hi Shane, this all worked perfectly but now i've gone to add new data to my data source and none of it is appearing in the nested gallery. I've disconnected/reconnected the data source but still nothing. Any idea what else i could try? Thanks!

[@Damski748]

Hey Shane, yet another great video. How can we make the second gallery interactive? selecting an item in the child gallery selects the parent record not the child?

[@ShanesCows]

Interesting. I really though the child gallery was interactive. Are you sure you don’t have a control over top or something?

[@prateekmathur6836]

Hi, can you please tell us what if we want to get the department and last name in the inner gallery.

[@jasmingutierrez6815]

Very nice! I would like to ask if this will work too in large data sets, say more than 50k items? Thanks in advance!

[@ShanesCows]

I doubt it but not sure. I haven’t tried.

[@cztop54]

As always a great and very understandable video! Of course, I have to ask...is there anyway to accordian this? Thanks always for your great work!

[@ShanesCows]

PRobably. But I don't have a good example off the top of my head. Hmmm. I will think on it.

[@abuyotam]

Hi Shane, does GroupBy work with the "'Created By'.DisplayName" column?

[@ShanesCows]

I don't think it is possible after some quick tests.

[@abuyotam]

@@ShanesCows Thanks.

[@LeahCreiglow]

Your videos are the best! I just can't find exactly what I am trying to do.. If I want to display my Customers and info from only their most recent order in a gallery how would I do that pulling from the two SP lists that have a one-to-many relationship. I just can't get Lookup or Filter or GroupBy to work. I can do this in Excel using Index and Match. Help!

[@jasonkirkdottech]

Awesome... How would I now Search data from a column in a that nested Data table?

[@henokmazengia1156]

Hi Shane, thank you so much for sharing this video. A question to you, I am trying to search for a SharePoint single line text values with comma delimited. For example, my Title column has 3 values in 3 rows with Test1 on first row, Test2 on second row, and Test3 on third row. How do I search for Test1 and test 2 at the same time so I can display the values for both rows? I am only able to search for each value once at a time.

[@tejaskshirsagar933]

Hi Shane , Can we add forms inside gallery as an item like nested galleries ? so that we can edit or change some value of the record by that form only any clue ?

[@ShanesCows]

I don't believe that you can. You will need to edit the data outside the gallery or switch to a more complicated gallery. Something like this. kzbin.info/www/bejne/rpjdn55qjdGDedE

[@tejaskshirsagar933]

@@ShanesCows Thank you 🤜🤛

[@anandasanyal]

Group By Person or Group field, Group By expects a text.. Not Person and Group.. Is there any work around..? Thanks for your support.

[@ShanesCows]

Not really. You could AddColumns to create a text column of the value then groupby from the added column

[@freeloader9090]

You're a lifesaver 👍

[@Gerberd4936]

Are you able to sort the names of the people in the DATA table you’re calling in the label?

[@ShanesCows]

Yes, put a SortByColumns around DATA kzbin.info/www/bejne/lX-mfZiIpdSnac0

[@Gerberd4936]

@@ShanesCows thank you I appreciate it a lot

[@adrianconnolly8568]

hi Shane, is there any chance you might make it look like a tree view with folders open and shuting, and show it collapsing and expanding, as well being able add to alter the tree view table in data verse while showing documents in SharePoint? based on SharePoint files with tags?

[@ShanesCows]

Adrian start with this where I made a document library app. Power Apps SharePoint Document Library Browser kzbin.info/www/bejne/q17FaapvmsZkoc0 should give you some ideas.

[@gserrano1088]

Hi! Thank you so much for this video! If for example, I have a gallery with employee records and it contains employee division, as well as other fields like systems, roles, etc... and every division has unique roles (so there will be duplicates since there are many different roles for each division) with this method I can group them so that my gallery only shows the divisions (so that someone can choose it in the first screen) but how can I do so that once chosen another screen will appear will all the info of the division chosen in another gallery?

[@colinbell7281]

Hi Shane, long shot ask but if i wanted to use this groupby and show the image how would i write that? it does not pull in for me in the same way as "thisitem.default image" it doesn't seem to treat the field the same

[@sueamin2788]

Thank you Shane for this very succinct technology video. Really helped me in the nick of time. A q. Can I group by Created By? (For me "Created By" or "Created By.DisplayName" or "Author.DisplayName" do not work. Any suggestion? Thanks again. SueA

[@edersonti]

Hello Shane ! About choice columns. Do I have to add it to a new column in my gallery for grouping to work?

[@ShanesCows]

I think so becaues you want to group on Choice but not 100% sure.

[@shanek9929]

I Found a work around bro Groupby(AddColumns(DataSourceName, "NewFieldName",Category.Value), "NewFieldName", "DATA")

[@lihtnes29]

Great topic! I'm implementing the same area where i have to group based on certain incident types. I have 6 incident types and out of which 3 has got data and those 3 types are grouped properly. I also want to bring the other 3 with 0 as a count value.. Is there a direct way on grouping to do that to have all the items of the group by column and show value 0 for the type which doesn't have value.

[@douglaspetersen8602]

Hi Shane Love your videos! I have a nested gallery that has text input fields and I am having trouble figuring out how to patch those inputs back to a collection using a single save button that is outside of the nested gallery. Is there a way to do this? For example in your video here if you had a text input instead of text label for email address and you wanted user to be able to change the email address from within the nested gallery.

[@galinagolant]

Thank you!!! I love this video!! Question - different dept have different numbers of employers (different numbers of records). Can the height of items in the parent gallery (with dept names) have different heights? Does my question make sense?

[@galinagolant]

@Charles-Antoine Dolbeau Thank you

[@ShanesCows]

Thanks for the support @Charles-Antoine Dolbeau 😀

[@lumilo7]

gracias capo ♥

[@ShanesCows]

De nada 🤩

[@MichaelDeBlasis1369]

For some reason GroupBy will not work on a sharepoint choice column I'm using. It says the value isn't a text when it clearly is. The column has no multiple selections allowed. I even created a new choice column called test and gave it some choices. Same error. Even stranger is I get that error on a plain Last Name text column too. But it works on the first name column. Maybe i just need sleep haha! Any ideas?

[@JohnDrogado69]

Hey, someone else commented this already. But you cant interact with the items in the nested gallery. When i want to access an item in the nested gallery it wont direct me to the detailscreen. is there a solution?

[@wrmbanks2260]

Brilliant video! I was wondering though, I want to create a gallery which lists users profile photos only within this nested gallery, sort of like a "Who's using the app" type scenario. Do you have to upload the photos to your data source, or can you use Office365.Users.UserPhotoV2?

[@jessicaho7555]

how can I only get the distinct values in the child gallery and do sortbycolumns? It keeps giving me an error that it is not supported.

[@monishkawale9052]

Hi can we use group by function on large SharePoint list

[@ShanesCows]

I don't believe that it is delegable. SOrry

[@monishkawale9052]

@@ShanesCows opps. Any alternative solution to achieve this ?

[@diegonavarro768]

Awesome content! I'm new to Powerapps and was wondering if you could help me to know how to use the addcomumns groupby functions for a 2 series column chart. Thanks in Advance!

[@ffa2107]

Hey Shane.. how can you use a function on the groupby collection? For example, counting the number of employees in each department?

[@louiscutmore]

Love this! Is it possible to sort in a specific order in one of those galleries? i.e. not ascending or descending ps. Hire me to reply to comments...not mine obviously!

[@ShanesCows]

I can pay in high fives? 😁 SortByColumns can do what you want. I haven't made a video on it but since I like you here is the link docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

[@louiscutmore]

@@ShanesCows deal ✋ Thanks for the link!

[@MichaelDeBlasis1369]

Def important to Note that Groupby had a recent syntax change. A simple but notworthy one. All column identifiers now use ' single quotes rather than" double quotes. YW! LOL

[@ShanesCows]

Thanks for the reminder, yeah they changed all of the column to use Display Names now. :)

[@MichaelDeBlasis1369]

@@ShanesCows Its so new that nobody has anything on this yet. I had to get it direct from MS docs. I even had to tell GPT to update its memory. So no worries. I was going nuts though hehe

[@vinimartins6]

Hi Shane. I have a problem, I have a list of products that repeat the products and I need to make another with the Sum. Example: eraser 2 pencil 1 eraser 4 pencil 2 And I need to return this: eraser 6 pencil 3 Do you know how can I do it? Thanks! Your videos are amazing!!

[@skovacsbarna]

I am also struggling with this problem!

[@skovacsbarna]

@Charles-Antoine Dolbeau gives me incorrect results, maybe the database is too large 50,000 lines?

[@AhmedBalfaqih]

Simply, wow.

[@ShanesCows]

Thanks :)

[@sundrapillay6668]

Thanks Shane

[@ShanesCows]

Always happy to help you Sundra!

[@huntjonh6701]

Hi Shane! Thanks for the info. Super Helpful! One question though. What if I want to use groupby function but the formula bar cannot find the sharepoint column that I want to use as the filter to group the data like example a sharepoint column set up as choices(sample for status of item) how can I make this work? Thank you so much!:)

[@shanek9929]

I Found a work around bro Groupby(AddColumns(DataSourceName, "NewFieldName",Category.Value), "NewFieldName", "DATA")

[@ffa2107]

@@shanek9929 trying to do this with a COUNTROWS function for the new groupby collection, cant get it to work. Any help would be apprecited

[@kristielaier6092]

@@shanek9929 Woohoo! Thank you!!

[@Dkizzle16]

how do you remove a grouped record from your collection with one click?

[@ShanesCows]

I don't have a solution for that. Grouping data is about viewing, I have never tried to edit or delete it.

[@franslombard3553]

@@ShanesCows do you have a solution for deleting an item from a nested gallery now ? Many thanks

[@varshanaithani8419]

hello shane, I have a question can we add a 3rd level nested galleries in a canvas app? anyone who can clarify please let me know your thoughts.

[@TheToni2123]

Thx

[@ShanesCows]

Happy to help 😎