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
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

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

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,854)