PowerApps GroupBy function and Nested Galleries

Подписаться 162 тыс.

Просмотров 74 тыс.

50% 1

This handy little function is when you want to group your data by a common value. In the example we use Employees and group by department. Then we have a gallery with a sub-gallery that shows the grouped data. So cool and handy.
Power Apps Consulting and Training at www.PowerApps911.com

Наука

Опубликовано:

14 апр 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 138

@patrikgajdos468 7 часов назад

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

@SushGS2 3 года назад

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 3 года назад

Glad it helped!

@sarahjarman5781 2 года назад

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

@ShanesCows 2 года назад

Glad to help Sarah 🐶

@sconsalter 2 года назад

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

@ShanesCows 2 года назад

Happy to help. Have a great day. 🐶

@libbyspaur605 3 года назад

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

@ShanesCows 3 года назад

I got your back. 🤩

@traceyblackmore852 3 года назад

Another great video! Thanks Shane, very beneficial.

@LNG87PA 2 года назад

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

@ShanesCows 2 года назад

Love it!

@rodrigopresencebagarolo2892 3 года назад

That's exactly what I needed, thank you!

@ShanesCows 3 года назад

You're welcome!

@joshwalker5419 3 года назад

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

@ShanesCows 3 года назад

Great to hear!

@dankirchner4460 Год назад

Great video Shane!!!

@ShanesCows Год назад

Thanks 😊

@zeedu1 2 года назад

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

@ShanesCows 2 года назад

You are most welcome

@Alan-wt1vo 3 года назад

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

@ShanesCows 3 года назад

Glad it was helpful!

@stwilhite1 3 года назад

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

@ShanesCows 3 года назад

Awesome Scott!

@freeloader9090 2 года назад

You're a lifesaver 👍

@jankutilek3954 2 года назад

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

@ShanesCows 2 года назад

Thanks Jan!

@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AvGRlruMVnI.html 🙂

@BhupendraSingh-nd4nb Год назад

Great Video, you are a life saver 🌻

@ShanesCows Год назад

Happy to help. Have a great day. 🐶

@emadaljundi7400 2 года назад

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

@ShanesCows 2 года назад

It's my pleasure

@iansuffield351 3 года назад

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!!)

@petereriksson6859 3 года назад

So nice Rock on and keep up the good things .

@ShanesCows 3 года назад

Thanks 😊

@Mx81 3 года назад

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

@ShanesCows 3 года назад

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

@briancollier3303 2 года назад

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."

@DaveMelton 2 года назад

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

@itdept1147 2 года назад

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?

@nilokillian 3 года назад

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

@DukeSr87 3 года назад

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

@ShanesCows 3 года назад

Awesome! 😎

@ffa2107 2 года назад

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

@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.

@lihtnes29 2 года назад

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.

@lumilo7 9 месяцев назад

gracias capo ♥

@ShanesCows 9 месяцев назад

De nada 🤩

@prateekmathur6836 3 года назад

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

@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?

@sundrapillay6668 3 года назад

Thanks Shane

@ShanesCows 3 года назад

Always happy to help you Sundra!

@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.

@varshanaithani8419 2 года назад

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.

@sarahjarman5781 2 года назад

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!

@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

@LeahCreiglow 3 года назад

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!

@AhmedBalfaqih 3 года назад

Simply, wow.

@ShanesCows 3 года назад

Thanks :)

@wrmbanks2260 2 года назад

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?

@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

@jaddfigaro7246 2 года назад

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

@JohnDrogado69 2 года назад

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?

@jenskemeijs 3 года назад

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 2 года назад

@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)

@cztop54 3 года назад

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 3 года назад

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

@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".

@diegonavarro768 2 года назад

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!

@jasonkirkdottech 2 года назад

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

@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

@jessicaho7555 2 года назад

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.

@MichaelDeBlasis1369 2 месяца назад

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?

@Damski748 3 года назад

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 3 года назад

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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-u-b8w9ea1kk.html should give you some ideas.

@danielavasques5952 11 месяцев назад

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 11 месяцев назад

GroupBy(Filter(DataSource, User = User()), "ColumnToGroupOn", "RestOfYourData") That is roughly how it should be structured. This video on Nesting might help. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-iG8SjWtX1yM.html

@danielavasques5952 11 месяцев назад

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

@ilikeurstyle2594 3 года назад

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 3 года назад

I talk about that here. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-49O284deOAI.html

@jasmingutierrez6815 3 года назад

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 3 года назад

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

@moyura2 2 года назад

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

@ShanesCows 2 года назад

Correct

@TheToni2123 3 года назад

Thx

@ShanesCows 3 года назад

Happy to help 😎

@frog5799 10 месяцев назад

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 10 месяцев назад

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.

@loisyyymarie952 11 месяцев назад

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 11 месяцев назад

GroupBy(Filter(datasource, logic), column, “DATA”) would 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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-W5Z61Q9Rehs.html

@edersonti 3 года назад

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

@ShanesCows 3 года назад

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

@shanek9929 3 года назад

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

@tejaskshirsagar933 2 года назад

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 2 года назад

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xgznk4XlPCo.html

@tejaskshirsagar933 2 года назад

@@ShanesCows Thank you 🤜🤛

@galinagolant 3 года назад

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?

@charles-antoinedolbeau5215 3 года назад

Yes you just need to select a "flexible height gallery"

@galinagolant 3 года назад

@@charles-antoinedolbeau5215 Thank you

@ShanesCows 3 года назад

Thanks for the support @Charles-Antoine Dolbeau 😀

@anandasanyal 2 года назад

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 2 года назад

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

@abuyotam 2 года назад

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

@ShanesCows 2 года назад

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

@abuyotam 2 года назад

@@ShanesCows Thanks.

@huntjonh6701 3 года назад

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 3 года назад

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

@ffa2107 2 года назад

@@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 10 месяцев назад

@@shanek9929 Woohoo! Thank you!!

@andreedmunds 2 года назад

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 2 года назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Gtzs1-IuXuY.html

@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!!

@louiscutmore 3 года назад

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 3 года назад

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 3 года назад

@@ShanesCows deal ✋ Thanks for the link!

@vinimartins6 3 года назад

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!!

@barnakovacs2339 3 года назад

I am also struggling with this problem!

@charles-antoinedolbeau5215 3 года назад

Groupby is exactly what you're looking for. In the label, just use: sum(ThisItem.DATA,quantity)

@barnakovacs2339 3 года назад

@@charles-antoinedolbeau5215 gives me incorrect results, maybe the database is too large 50,000 lines?

@charles-antoinedolbeau5215 3 года назад

@@barnakovacs2339 Yes the SUM is not delegable (the processing is done by your app and not by SharePoint), so it will sum only the 2000 first rows of DATA (if you increase the default value from 500 to 2000).