cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vaika160
Frequent Visitor

Combining Filter, group by and Concatenate in Gallery

It seems like it should be simple but I cannot figure this out.
Filter RequestDate >= today()

 

I have a table. this table has other fields.

RequestDate   Name    Office     Field 1   Field2 ...

2021/04/22    Name1   Office1
2021/04/23      Name2   Office2

2021/04/22      Name3   Office1

2021/04/23      Name4  Office1

 

What I need to accomplish:

RequestDate    Name                       Office
2021/04/22     Name1 , Name3        Office1
2021/04/23     Name4                     Office1
2021/04/23     Name2                   Office2

Not sure how to do it for these fields only?
Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @vaika160 ,

The syntax is valid - I just ran it on a collection off a list here and it worked perfectly.

Check your column names are actually what you believe - go to SharePoint List Settings, select the field name and then look at the end of the string at the top.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

13 REPLIES 13
WarrenBelz
Super User
Super User

Hi @vaika160 ,

I cannot test this presently, but something like this should do it.

AddColumns(
   GroupBy(
      YourTableName,
      "RequestDate",
      "OtherData"
   ),
   "Name1",
   Concat(
      OtherData,
      Name & ","
   ),
   "Office1",
   First(OtherData).Office
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

WarrenBelz
Super User
Super User

Hi @vaika160 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi WarrenBelz,

I really do not understand how to use your suggestion, sorry.

Like in my table name1 and Office1 is the field value.

What other data means? 

Can you, please, explain in more details?

 

Thank you.

 

@vaika160 ,

It will be the Items of your gallery and is using the GroupBy function. Substitute your Table name where I have that and your field name where I have Name.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

WarrenBelz
Super User
Super User

Hi @vaika160 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

I am still cannot make it work, sorry

I really thank you for your help and follow up with me on this issue but I can not put 2 & 2 together. 

My SP list has these columns: Employee, DateRequested, Office, ApprovedBy, ApproveDate, CheckSubmitted, SubmittedDate, Passed

I have a collection based on this list : 

ClearCollect(colReservationsFilter(OfficeBooking DateRequested >= Today() ) )

 
for this grouping I assume I have to create a new collection based on already existing one as I need the same filter?

So I was trying to follow what you suggested. 

 
 

ClearCollect colReservationDay,
     AddColumns
            GroupBycolReservations"DateRequested""Office" ),
        "EmployeeName"Concat( Employee.DisplayName & "," ),
        "Office",
First(Office)
        )
)
 
I still do not get what is this and why it has to be used? 
First(OtherData).Office

Thanks a lot.
Appreciate your help.
 
 Also in Concat function I do not think I need Office added. It should be a separate column.
Please, correct me if I am wrong 
 

Hi @vaika160 ,

Firstly, it is very useful to understand the GroupBy() function. The key is that you need a "final" column to hold a table where the "rest of the fields" matching the group are stored. You can then utilize that table for an AddColumns function and do things like totals and counts, or in your case, a Concat list. If you want to group by DateRequested AND Office (all unique combinations of these), you would do this

ClearCollect(  
   colReservationDay,
   AddColumns( 
      GroupBy( 
         colReservations, 
         "DateRequested", 
         "Office",
         "OtherData"
      ),
      "EmployeeName", 
      Concat( 
         OtherData,
         Employee.DisplayName & "," 
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @WarrenBelz ,

I am still getting an error that Group By function has some invalid arguments.
I have checked all brackets - they look fine to me.

Thank you for your explanation re: Other Data - that it is kind of a table. It was very helpful.


Thank you.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (3,401)