cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA
Helper II
Helper II

Undoing GroupBy for ThisItem in gallery

Hi all,

 

I have a collection I built for a gallery that contains a number of nested GroupBy statements to aggregate up time entry data by week. However, I want to allow a user to drill down and ungroup that data to get individual days again, and view them in another gallery.

 

Source data

DateNameProjectTaskHours
3/22/21JaneProject 1Dev5
3/22/21JaneProject 1Meetings3
3/23/21JaneProject 1Dev2

 

Aggregated data - combine different project tasks into one project, and days up to a week. Date is just removed entirely, because the week is already limited by a date picker.

NameProjectHours
JaneProject 110

 

On clicking an arrow in the gallery, I want to drill down and de-aggregate the data back to its original form.

 

I looked into flexible height galleries to maybe just expand the data back out (which would be great), or taking the user to another screen and using a second gallery. I wasn't able to get the first to work, and I'm not sure how to do the second, because I'm not sure how to mix Ungroup() with the ThisItem syntax.

 

If you're curious, the aggregated collection formula is in the spoiler.

 

Spoiler
ClearCollect(
    TimeEntryDataWeekly,
    AddColumns(
        GroupBy(
            AddColumns(
                GroupBy(
                    ShowColumns(
                        AddColumns(
                            Filter('Project Approval','Project Approval (Views)'.'Time Entries for Approval',
                                msdyn_date > VarSelectedStartDate && msdyn_date < VarSelectedEndDate
                            ),
                            "DateString",Text('msdyn_date',ShortDate),
                            "ResourceName",msdyn_bookableresource.Name,"ProjectName",Project.'Project Name'
                        ),
                        "DateString","ResourceName","ProjectName","msdyn_ProjectTask","msdyn_TimeEntry","msdyn_costquantity"
                    ),
                    "DateString","ResourceName","ProjectName","Other"
                ),
                "DayHours",Sum(Other,msdyn_costquantity)
            ),
            "ResourceName","ProjectName","Other"
        ),
        "WeekHours",Sum(Other,DayHours)
    )
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @RMDNA ,

Have you tried (I cannot test this presently)

ClearCollect(
   colUnGrouped,
   UnGroup(
      TimeEntryDataWeeky,
      "DateString",
      "ResourceName",
      "ProjectName"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

6 REPLIES 6
WarrenBelz
Super User III
Super User III

Hi @RMDNA ,

Have you tried (I cannot test this presently)

ClearCollect(
   colUnGrouped,
   UnGroup(
      TimeEntryDataWeeky,
      "DateString",
      "ResourceName",
      "ProjectName"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Hi @WarrenBelz: got the de-aggregation working by referencing the column "Other" that held the rest of the records. 

 

Now I need to only show that data specific to the aggregated record I'm drilling down on. Presumably I somehow set the parent data of this second gallery to the selection from the first one?

 

For example, the lower gallery now contains my de-aggregated data, but I want to show only the relevant entries for whatever record I select in the top gallery.

 

asdf.PNG

@RMDNA ,

That is why I said I could not test it at the time - I suspected exactly that. Try them one at a time and see if it "unwinds" it all.

@WarrenBelz - I do get all the data I need when I ungroup, so that part is successful.

 

Now I just need to get the second gallery to filter on the first gallery - when I select the arrow for a week/project, only show those related unaggregated data in the gallery below.

 

Am I misunderstanding something?

@RMDNA ,

Please ignore my last post - I think I cross-read some threads.

What Filter have you tried - you simply need to filter the second list based on the Selected item's field from the first list.

@WarrenBelz - I did some experimenting and got it working. Checked if a single unaggregated entry's GUID existed in the concatenated list of aggregated GUIDs. Thanks for the help!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,100)