cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbunge
Advocate IV
Advocate IV

GroupBy only if certain value in column

Use case: SharePoint list/Collection with a number of columns. One of those columns, call it ToBeGrouped, has single letter values, and I would like to GroupBy only if that value is "G". Is this at all possible? Everything I've read seems to point to "all-or-nothing", but I'm hoping someone might have some ideas.

 

Here's what the data set starts as:

Col1  Col2  Col3  ToBeGrouped  Col5

5        7        4             G                5

5        7        4             G                5

7        2        6             F                5

6        8        4             G                5

5        7        4             B                5

5        7        4             B                5

5        7        4             G                5

 

And this should be the end result:

5        7        4             G               15

7        2        6             F                5

6        8        4             G                5

5        7        4             B                5

5        7        4             B                5

 

The three lines that had G in ToBeGrouped and same values in Col1, Col2, and Col3 were grouped, and the value in Col5 was summed. The other columns were left ungrouped.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Adrian_Celis
Super User
Super User

Hi @rbunge 

GroupBy will group everything in a column. But this workaround may help you in your problem.

 

Let's say that the data source that you've show is in a collection named Collection 1. Try this code in a button so that it only groups a specific letter in the "ToBeGrouped" column.

 

 

//Use GroupBy and Aggregate all records from Collection 1 into Collection 2
ClearCollect(Collection2,GroupBy(Collection1,"Col1","Col2","Col3", "ToBeGrouped","GroupedColumn"));


//Get the aggregated record for letter "G" only and put into Collection 3
Clear(Collection3);
Collect(Collection3,
Filter(DropColumns(AddColumns(Collection2,"Col5",Sum(GroupedColumn,Col5)),"GroupedColumn"), ToBeGrouped = "G")
);

//Collect the rest of the records that are not aggregated from Collection 1 and put into Collection 3
ForAll(Filter(Collection1,ToBeGrouped <> "G"),
Collect(Collection3,ThisRecord)
)

 

You should see the results in Collection3.

 

 

View solution in original post

Hello @Adrian_Celis-

 

Apologies for the late reply -- am only just now getting back to this page... for some reason, I thought I used to get email alerts when there were responses, but didn't see anything.

 

This appears to work exactly as designed! I'll test it with my actual production data and let you know, but I very much appreciate the help! I'll also more thoroughly digest your method, as I'm sure there are techniques for future applications.

 

I'll accept it as the solution and I'll advise if any questions after I test with actual data.

 

Thanks again!

View solution in original post

13 REPLIES 13
Adrian_Celis
Super User
Super User

Hi @rbunge 

GroupBy will group everything in a column. But this workaround may help you in your problem.

 

Let's say that the data source that you've show is in a collection named Collection 1. Try this code in a button so that it only groups a specific letter in the "ToBeGrouped" column.

 

 

//Use GroupBy and Aggregate all records from Collection 1 into Collection 2
ClearCollect(Collection2,GroupBy(Collection1,"Col1","Col2","Col3", "ToBeGrouped","GroupedColumn"));


//Get the aggregated record for letter "G" only and put into Collection 3
Clear(Collection3);
Collect(Collection3,
Filter(DropColumns(AddColumns(Collection2,"Col5",Sum(GroupedColumn,Col5)),"GroupedColumn"), ToBeGrouped = "G")
);

//Collect the rest of the records that are not aggregated from Collection 1 and put into Collection 3
ForAll(Filter(Collection1,ToBeGrouped <> "G"),
Collect(Collection3,ThisRecord)
)

 

You should see the results in Collection3.

 

 

v-bofeng-msft
Community Support
Community Support

Hi @rbunge :

I assume there is a table something like:

ClearCollect(
  TheCollection,
  {Col1:5,Col2:7,Col3:4,ToBeGrouped:"G",Col5:5},
  {Col1:5,Col2:7,Col3:4,ToBeGrouped:"G",Col5:5},
  {Col1:7,Col2:2,Col3:6,ToBeGrouped:"F",Col5:5},
  {Col1:6,Col2:8,Col3:4,ToBeGrouped:"G",Col5:5},
  {Col1:5,Col2:7,Col3:4,ToBeGrouped:"B",Col5:5},
  {Col1:5,Col2:7,Col3:4,ToBeGrouped:"B",Col5:5},
  {Col1:5,Col2:7,Col3:4,ToBeGrouped:"G",Col5:5}
)

You could try:

ShowColumns(
    AddColumns(
        GroupBy(
            TheCollection,
            "Col1",
            "Col2",
            "Col3",
            "ToBeGrouped",
            "NewGroup"
            ),
        "Col5",
        Sum(NewGroup,Col5)
    ),
"Col1","Col2","Col3","ToBeGrouped","Col5")

Best Regards,

Bof

Hello @Adrian_Celis-

 

Apologies for the late reply -- am only just now getting back to this page... for some reason, I thought I used to get email alerts when there were responses, but didn't see anything.

 

This appears to work exactly as designed! I'll test it with my actual production data and let you know, but I very much appreciate the help! I'll also more thoroughly digest your method, as I'm sure there are techniques for future applications.

 

I'll accept it as the solution and I'll advise if any questions after I test with actual data.

 

Thanks again!

Just had a go at this in production and it worked like a charm! Thanks again @Adrian_Celis!

rbunge
Advocate IV
Advocate IV

Morning-

 

The plot thickens... we have a new requirement for this, and I've not been able to work out the solution using @Adrian_Celis's excellent answer as a starting point...

 

In addition to Cols 1, 2, and 3, each now has a "Revised" sister column. In other words, Col1a is the "original" value (label) and Col1b is the "revised" value (text input). The logic should be that IF there's something in the "revised" "b-version" of the column pair, then THAT value should be used for GroupBy, otherwise the "original" "a-version" should be used.

 

Dizzy yet?

 

Modifying the original graphic:

 

Col1a  Col1b  Col2a  Col2b  Col3a  Col3b  ToBeGrouped  Col5

5          6          7                      4                          G                5

5                      7                      4                          G                5

7                      2                      6                          F                5

6                      8                      4                          B                5

5                      7                      4                          G                5

4          5          7                      4                          G                5

5                      7                      4                          B                5

 

And this should be the end result:

6                    7                      4                          G                5

5                    7                      4                          G                5

7                    2                      6                          F                5

6                    8                      4                          B                5

5                    7                      4                          G                10

5                    7                      4                          B                5

 

The first two lines are no longer grouped because the 5 in a became a 6 in b, making them different, and the last two penultimate lines ARE now grouped because the 4 in a became a 5 in b, making them equal.

 

I've tried with intermediate collections, following @Adrian_Celis's breadcrumbs, but no joy.

 

Any assistance would be much appreciated!

Hi @rbunge 

We can tell the code to add columns "Col1c","Col2c","Col3c". In these columns we use the Coalesce function to return the value which is not blank starting with b to a. In the end you will have something like this.

 

//From Collection 1, add another column "c" to determine which one is not blank from b to a
With({CollectionWithAddedColumns:   AddColumns(Collection1,"Col1c",Coalesce(Col1b,Col1a),"Col2c",Coalesce(Col2b,Col2a),"Col3c",Coalesce(Col3b,Col3a))},
//Use GroupBy and Aggregate all records from Collection 1 into Collection 2
ClearCollect(Collection2,
GroupBy(
    CollectionWithAddedColumns
    ,"Col1c","Col2c","Col3c", "ToBeGrouped","GroupedColumn"
    )
);

//Get the aggregated record for letter "G" only and put into Collection 3
Clear(Collection3);
Collect(Collection3,
Filter(DropColumns(AddColumns(Collection2,"Col5",Sum(GroupedColumn,Col5)),"GroupedColumn"), ToBeGrouped = "G")
);

//Collect the rest of the records that are not aggregated from Collection 1 and put into Collection 3
ForAll(DropColumns(Filter(CollectionWithAddedColumns,ToBeGrouped <> "G"),"Col1a","Col2b","Col2a","Col1b","Col3a","Col3b"),
Collect(Collection3,ThisRecord)
))

Hi @Adrian_Celis -

 

Thanks for the ongoing help -- much appreciated!

 

Love the coalesce idea (a very underused function!)...

 

The only issue I see is that both a and b will be non-blank in cases where user enters a value in b (first and second-to-last lines in example above). In these cases, I have to take the b value. Thoughts?

Hi @rbunge 

Yes that is exactly what the Coalesce function will do.

If you do Coalesce(b,a):

If both a & b have values it will give you b

If only letter a has a value, it will give you a

Am I getting what you are looking for?

Ah, may have misunderstood -- I'll have a go in the app and let you know later today. Thanks!

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

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