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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,108)