Power Platform Community Forum Thread Details

Hi,

I have this Employees dataverse table, and I need to split the values in the column TeamID (separated with commas).

For each value, one new row in a collection.

This is the collection Result that I need:

Thanks for any help!

Categories:

Try manipulating your collection by adding a nested table with AddColumns() and Split(), then use ungroup().

The simplest way is like this:

Ungroup(
    AddColumns(
        col_your_table,
        newGroup,
        Split(
            TeamID,
            ","
        )
    ),
    newGroup
)

If you don't care about the column header for the Team ID. It looks like this in a Table control:

If you want to have proper column names then try it like this:

Step one: Save your example collection in line with a With()

Step Two: Convert the TeamID column into a Nested Table with separate columns for Name and ID

Create a nested "grouped" table/collection with AddColumns() and Split() and some other functions

Step Three: Ungroup the Grouped Collection

My whole expression code looks like this:

With(
    {
        //this With() step is option, i'm just using it to save your specific example table
        col_A: [
            {
                Name: "Andy",
                TeamID: "1000,1500,2000"
            },
            {
                Name: "Tom",
                TeamID: "1000,1200"
            }
        ]
    },
    With(
        {
            //convert the original collection into a 'grouped' collection, by splitting the TeamID into an array and adding a column with the Row Name and another column for each specific Team ID
            col_B_grouped: 
            
            AddColumns(
                col_A,
                group,
                With(
                    //save the top level 'Name' column value as 'rowName' for easy reference
                    {rowName: Name},
                    AddColumns(
                        //convert TeamIDs to array by comma split
                        Split(
                            TeamID,
                            ","
                        ),
                        //add the person's name to the nested table
                        name,
                        rowName,
                        //add the discrete 'TeamID' values as their own column
                        'Team ID',
                        Value
                    )
                )
            )
        },
        //create a new collection just containing the new nested table we created above
        Ungroup(
            col_B_grouped,
            group
        )
    )
)

If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff | YouTube: Mark's Power Stuff |