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 |
| ||||
Captain Jack Sparrow | gold plan |
| ||||
Pepper Potts | suite 3000 |
| ||||
Peter Pan | gold plan |
|
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!
Solved! Go to 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
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
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,
User | Count |
---|---|
256 | |
111 | |
95 | |
48 | |
40 |