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

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

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

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

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
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (2,464)