cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TB05
Helper II
Helper II

GroupBy

Hi,  I have a collection and it is Grouped based on the EmployeeID.  And I'm summing up the pay for the week.

But within that Grouping, there is a comments section.  How do I get all the comments to combine into one field and be distinct?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

@TB05 ,

To obtain that, there is small change in my previous formula. Put "," instead of char(10). I supposed that the delimatator is ",".

And on text property of the label use (you may need to adjust the formula):

With(
    {
        CommentsCollection: Split(
            AllCommentsForEmployeeID,
            ","
        )
    },
    Concat(
        ForAll(
            Distinct(
                CommentsCollection,
                Result
            ) As temp,
            CountIf(
                CommentsCollection,
                Result = temp.Result
            ) & " " & temp.Result
        ),
        Value & ", "
    )
)

 

View solution in original post

So, in other word, you don't need the number of appearence for each word? Check this out...

With(
{
CommentsCollection: Split( 
ThisItem.AllCommentsForEmployeeID,
","
)
},
Concat(
Distinct(
CommentsCollection,
Result
),
Result & ", "
)
)

 

View solution in original post

7 REPLIES 7
gabibalaban
Super User
Super User

@TB05 ,

Based on your description, I supose that you grouped by the datasource and sum pay for the week using something like:

 

AddColumns(
      GroupBy(YourDataSource,"EmployeeID","Result"),
      "PayForTheWeek",
      Sum(Result,PayForTheWeekColumn)
)

 

 Why don' t you add a new column to concat the comments column from each group by like this:

 

AddColumns(
      GroupBy(YourDataSource,"EmployeeID","Result"),
      "PayForTheWeek",
      Sum(Result,PayForTheWeekColumn),
      "AllCommentsForEmployeeID",
      Concat(Result,Comments & Char(10))
)

 

I used Char(10) to put comments each onnew line.

Hope it helps !

That worked great, one other question...so now in the text box, I have the following:

TB05_0-1634408504234.png

So, is there a way to count the DR and the Tier 1, Tier 2 and have them display like this:

4 DR, 2 Tier 1, 1 Tier 2, 1 Tier 3, 1 MSK or something like that??

 

Thanks for your quick response.

@TB05 ,

To obtain that, there is small change in my previous formula. Put "," instead of char(10). I supposed that the delimatator is ",".

And on text property of the label use (you may need to adjust the formula):

With(
    {
        CommentsCollection: Split(
            AllCommentsForEmployeeID,
            ","
        )
    },
    Concat(
        ForAll(
            Distinct(
                CommentsCollection,
                Result
            ) As temp,
            CountIf(
                CommentsCollection,
                Result = temp.Result
            ) & " " & temp.Result
        ),
        Value & ", "
    )
)

 

View solution in original post

TB05
Helper II
Helper II

Thank you, that worked great!  Only had to change Split(ThisItem.AllComments......

TB05
Helper II
Helper II

Sorry to bother you, what if I just want to see in the text field, one word.  I have another text field that has 

multiple RPI, RPI, RPI and I only want to show one.  I tried to work your code to show one RPI but not successful.

So, in other word, you don't need the number of appearence for each word? Check this out...

With(
{
CommentsCollection: Split( 
ThisItem.AllCommentsForEmployeeID,
","
)
},
Concat(
Distinct(
CommentsCollection,
Result
),
Result & ", "
)
)

 

View solution in original post

TB05
Helper II
Helper II

Thanks Again!  That worked great.  I have applied it to multiple text fields.

Have a great weekend!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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