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

Group by with conditions

Dear community

 

I have a SharePoint list that is structured like this:

employee (person)project (lookup)projectName (text)projectId (number)weekDate (lookup)weekDateId (number)
Peter Panget rid of fairiesget rid of fairies12022-05-231
Captain Jack Sparrowgold plangold plan22022-05-231
Pepper Pottssuite 3000suite 300032022-05-231
Peter Panget rid of fairiesget rid of fairies12022-05-302
Peter Pangold plangold plan22022-05-231
Peter Pangold plangold plan22022-05-302
..................

 

I would need to get it grouped by employees and project. The end result should look something like this:

 

employeeprojectbookings
Peter Panget rid of fairies
2022-05-232022-05-30......
Captain Jack Sparrowgold plan
2022-05-23.........
Pepper Pottssuite 3000
2022-05-23.........
Peter Pangold plan
2022-05-232022-05-30......

 

Does anyone know how I can achieve this in Power Apps? 

 

Apologies for not providing you with some sort of code that I already tried it with. That is just because I have no idea on how to get started with this 😔 If I group by projects, I still have the problems that the all the dates are in separate rows. If I group by employees it's the same... and if I group by dates then everything is mixed together. None of that works for me 🙁

 

Thanks for your help in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @mrQ you can do this is one step but i will break this down as such

 

Grouping as you need it, by employee then project:

ClearCollect(
    colGroupedEmpProject,
    AddColumns(
        GroupBy(
            youList,
            "emp",
            "proj",
            "Employees"
        ),
        "Dates",
        Employees.weekdate //your weekdate column
    )
)

 

add it to a gallery and add the labels

label1 = ThisItem.emp

label2 = ThisItem.proj

label3 = Concat(ThisItem.Dates,weekdate," ")

 

Result:

rubin_boer_0-1653571344597.png

 

Hope it helps,

R

View solution in original post

2 REPLIES 2
rubin_boer
Super User
Super User

hi @mrQ you can do this is one step but i will break this down as such

 

Grouping as you need it, by employee then project:

ClearCollect(
    colGroupedEmpProject,
    AddColumns(
        GroupBy(
            youList,
            "emp",
            "proj",
            "Employees"
        ),
        "Dates",
        Employees.weekdate //your weekdate column
    )
)

 

add it to a gallery and add the labels

label1 = ThisItem.emp

label2 = ThisItem.proj

label3 = Concat(ThisItem.Dates,weekdate," ")

 

Result:

rubin_boer_0-1653571344597.png

 

Hope it helps,

R

Hi @rubin_boer 

 

Thanks for your response.


That already looks promising. However, the employee column is a person field and I can't unfortunately group by that. I also have some choice fields that I didn't mention in above example, but I don't seem to be able to add them neither. Do you know why, respectively how to get around it?

 

** EDIT **

I realized I can get around it by using AddColumns to show the desired field of the employee(person) column. 

 

ClearCollect(
    colGroupedEmpProject,
        GroupBy(
            AddColumns(
                myList,
                "employeeDisplayName",
                employee.DisplayName
            ),
            "employeeDisplayName",
            "projectName",
            "Employees"
        )
)

 

Thank you for the solution. Easier than expected when you know that you can group by two columns within one groupBy command 🙂


Kind regards,

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,826)