cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrQ
Helper V
Helper V

GroupBy various dates into month column

Hey there

 

I have the following source:

NameDateCategory
Car4/9/2020 12:00 AMLand
Bicycle5/25/2020 12:00 AMLand
Jetski5/4/2020 12:00 AMWater
Boat7/13/2020 12:00 AMWater
Ship7/4/2020 12:00 AMWater
Airplane7/20/2020 12:00 AMAir
Truck7/25/2020 12:00 AMLand

 

Out of the above source, is it possible to get a collection which looks something like the following?:

 

MonthCategories
April
CategoryTransactions
Land
NameDateCategory
Car4/9/2020 12:00 AMLand
May
CategoryTransactions
Land
NameDateCategory
Bicycle5/25/2020 12:00 AMLand
Water
NameDateCategory
Jetski 5/4/2020 12:00 AMWater
July
CategoryTransactions
Water
NameDateCategory
Boat7/13/2020 12:00 AMWater
Ship7/4/2020 12:00 AMWater
Air
NameDateCategory
Airplane7/20/2020 12:00 AMAir
Land
NameDateCategory
Truck7/25/2020 12:00 AMLand

 

Kind regards,

1 ACCEPTED SOLUTION

Accepted Solutions
rebeccas
Community Champion
Community Champion

@mrQ 

 

You can but you can make it display in galleries like that. Not sure what you need it to do. Here is how:

 

Gallery 1:

 

SortByColumns(GroupBy(AddColumns(DataSource, "DateMonth", Month(DateField),"DateYear",Year(DateField)),"DateMonth","DateYear","GrpByDAte"),

"DateYear",Ascending,"DateMonth",Ascending)

 

This gallery can have two labels in it: 

1)Switch(ThisItem.DateMonth,
1, "Jan",
2, "Feb",
3, "Mar",
4, "Apr",
5,"May",
6, "Jun",
7, "Jul",
8, "Aug",
9, "Sep",
10, "Oct",
11, "Nov",
12, "Dec",
"")

 

2) ThisItem.DateYear

 

The OnSelect for them needs to be: Set(varMonth, ThisItem.DateMonth); Set(varYear, ThisItem.DateYear)

 

Then you can have a second gallery that groups by the categories but also filters for only ones who have the Month and Year matching the variable you set on Gallery 1 OnSelect.--- The Filter portion would be something like: Filter(DataSource, Month(DateField) = varMonth && Year(DateField) = varYear)

 

The third gallery could not have any grouping...just filtering off the date variables and then a variable set off the "Category" selected in Gallery 2. It just really depends on what all you need do with it...there are a few ways you could make this work. 

 

The one I did just has several layers of filtering so it gets a similar result you are wanting but a different way of going about it. Just have to think outside the box. 

 

I have one that does a collection of all the data and each time they make a selection (from a gallery) it removes the items that do not fit that criteria....allowing them to filter multiple fields. 

 

View solution in original post

4 REPLIES 4
Drrickryp
Super User
Super User

Hi @mrQ , 

You are requesting a three level nested gallery.  At the present time, there is a maximum of two levels permitted, only a single gallery and subgallery.   This feature has been requested as a new feature but there was a work around for it posted here. https://powerusers.microsoft.com/t5/Building-Power-Apps/Looking-for-ideas-on-a-workaround-for-the-ne... .  If you want to vote for this feature to be added, please go to https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Nested-Galleries-Expand-Limit-to-At-Least-3-Nes...  in the Ideas section of the forum.

rebeccas
Community Champion
Community Champion

@mrQ 

 

You can but you can make it display in galleries like that. Not sure what you need it to do. Here is how:

 

Gallery 1:

 

SortByColumns(GroupBy(AddColumns(DataSource, "DateMonth", Month(DateField),"DateYear",Year(DateField)),"DateMonth","DateYear","GrpByDAte"),

"DateYear",Ascending,"DateMonth",Ascending)

 

This gallery can have two labels in it: 

1)Switch(ThisItem.DateMonth,
1, "Jan",
2, "Feb",
3, "Mar",
4, "Apr",
5,"May",
6, "Jun",
7, "Jul",
8, "Aug",
9, "Sep",
10, "Oct",
11, "Nov",
12, "Dec",
"")

 

2) ThisItem.DateYear

 

The OnSelect for them needs to be: Set(varMonth, ThisItem.DateMonth); Set(varYear, ThisItem.DateYear)

 

Then you can have a second gallery that groups by the categories but also filters for only ones who have the Month and Year matching the variable you set on Gallery 1 OnSelect.--- The Filter portion would be something like: Filter(DataSource, Month(DateField) = varMonth && Year(DateField) = varYear)

 

The third gallery could not have any grouping...just filtering off the date variables and then a variable set off the "Category" selected in Gallery 2. It just really depends on what all you need do with it...there are a few ways you could make this work. 

 

The one I did just has several layers of filtering so it gets a similar result you are wanting but a different way of going about it. Just have to think outside the box. 

 

I have one that does a collection of all the data and each time they make a selection (from a gallery) it removes the items that do not fit that criteria....allowing them to filter multiple fields. 

 

View solution in original post

Thanks for the hint.

 

How could I achieve it with 2 nested tables?

just saw this. I'm going to try it out tomorrow, thanks!

I'll keep you posted

 

** Update **

This works great. Thank you!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,350)