GroupBy only if certain value in column

[rbunge]

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.

[Adrian_Celis · Accepted solution]

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]

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

[rbunge · Accepted solution]

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!

[rbunge]

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

[rbunge]

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!

[Adrian_Celis]

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)
))

[rbunge]

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?

[Adrian_Celis]

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?

[rbunge]

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