Power Platform Community Forum Thread Details

Hi friends,

I have, what I feel like, is a pretty simple need, but have exhausted all my usual help pathways (forums, google, chatGPT, etc).

I have a sharepoint list that contains projects. Within this list is a column named ProductLineAffected. This is a single line of text column type and usually only contains one item. There are instances where there may be more than one product line affected. For example, most lines will have just "Product 1" or "Product 5" listed in that column, but some have more than one like "Product 1, Product 3", etc. separated with a comma.

I have a pie chart that shows the product lines affected utilizing a grouping and count value to get the number and grouped product line. My problem is that the current formula I use to get the grouped items, groups all the singular items correctly, but I haven't found a "Split" formula to work to split the ones that have multiple items listed in the ProductLinesAffected column so that they can be grouped with the other single line and grouped entries.

MyActiveData - this collection contains all records (from the "Active Projects" Sharepoint list) and has multiple columns of data.

myProdLine - this is the simplified collection and only contains "Count Value" and "ProductLineAffected" columns

Example data in myProdLine:

Count Value ProductLineAffected

1 Product 1

1 Product 3

3 Product 4

2 Product 6

1 Product 5

1 Product 4, Product 6

Here's my grouping formula:

ClearCollect(
 myProdLine,
 DropColumns(
 AddColumns(
 GroupBy(
 MyActiveData,
 "ProductLineAffected",
 "GroupedItems"
 ),
 "Count Value",
 CountRows(GroupedItems)
 ),
 "GroupedItems"
 )
);

What I'm trying to get to is this:

Future data in myProdLine:

Count Value ProductLineAffected

1 Product 1

1 Product 3

3 4 Product 4

2 3 Product 6

1 Product 5

~~1 Product 4, Product 6~~

I need to use this data in my pie chart and when it creates a new grouped item like the last line, it looks like a new type of product line rather than just adding to the proper ones above it.

Thank you in advance!!

Categories:

Building Power Apps

Hi @JScherff ,

So, you will need to split multiple products in the collection MyActiveData and then group them into the other collection.

I created a test list to make a demo:

When generating the MyActiveData collection, I used below formula:

Clear(MyActiveData);
ForAll(
 AddColumns(
 Products,
 "products",
 Split(
 ThisRecord.Title,
 ","
 )
 ) As AP,
 ForAll(
 AP.products As PP,
 Collect(
 MyActiveData,
 {Product: PP.Value}
 )
 )
)

and get singular product in line items of the collection:

So, then you can get the result table using below formula:

ClearCollect(
 myProdLine,
 DropColumns(
 AddColumns(
 GroupBy(
 MyActiveData,
 "Product",
 "GroupedItems"
 ),
 "Count Value",
 CountRows(GroupedItems)
 ),
 "GroupedItems"
 )
);

Best regards,