Group by with conditions

[mrQ]

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 Pan	get rid of fairies	get rid of fairies	1	2022-05-23	1
Captain Jack Sparrow	gold plan	gold plan	2	2022-05-23	1
Pepper Potts	suite 3000	suite 3000	3	2022-05-23	1
Peter Pan	get rid of fairies	get rid of fairies	1	2022-05-30	2
Peter Pan	gold plan	gold plan	2	2022-05-23	1
Peter Pan	gold plan	gold plan	2	2022-05-30	2
...	...	...	...	...	...

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

employee	project	bookings
Peter Pan	get rid of fairies	2022-05-23 2022-05-30 ... ...
Captain Jack Sparrow	gold plan	2022-05-23 ... ... ...
Pepper Potts	suite 3000	2022-05-23 ... ... ...
Peter Pan	gold plan	2022-05-23 2022-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!

[rubin_boer · Accepted solution]

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:

Hope it helps,

R

[mrQ]

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,