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

@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 II
Super User II

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.

@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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,419)