Split Choice field values into individual rows

I will go straight to the point.

I have a SharePoint list with below fields/columns:

One employee (Person field) can be a part of single department or multiple departments (choice field). I want to separate employee by each department. I want the above data to be converted to below data in Power Apps.

In order to achieve this functionality in PowerApps, I have to use Collection and ForAll functions.

Lets give a try!

The below code is used to extract the choice field value from each row along with employee display name.

Clear(CollBU); 

Clear(CollFormattedData); 

//Loop through each item from original Data 

ClearCollect( 

     CollBU, 

     AddColumns( 

         BusinessUnit, 

         "BU", 

         Collect(

            CollFormattedData, 

             ForAll( 

                 Departments, 

                 { 

                     Values: Value, 

                     Employee: Employee.DisplayName 

                 } 

             ) 

         ) 

     ) 

);

Let’s break down the Power Apps code snippet you provided:

1. Clear(CollBU);:

   • This line clears the existing data in a collection named CollBU.
   • It ensures that the collection starts fresh before adding new data.

2. Clear(CollFormattedData);:

   • Similarly, this line clears the existing data in another collection named CollFormattedData.

3. ClearCollect(:

   • The ClearCollect function is used to create or update a collection.
   • In this case, it creates a collection named CollBU.

4. AddColumns(:

   • The AddColumns function adds new columns to the CollBU collection.
   • It takes the BusinessUnit data source (assumed to be a SharePoint list or other data) as the base collection.

5. "BU",:

   • This specifies the name of the new column to be added in CollBU.
   • The new column will store formatted data related to business units.

6. Collect(:

   • The Collect function creates a new collection (in this case, CollFormattedData).
   • It iterates through each item in the Departments collection (assumed to be another collection or data source).

7. ForAll(:

   • The ForAll function iterates through each item in the Departments collection (choice field).
   • For each department, it creates a record (a set of key-value pairs) with two properties:
     • Values: Maps to the value of the department (assumed to be a column in the Departments collection).
     • Employee: Maps to the display name of the employee (assumed to be a property of the Employee object).

8. Resulting Collections:

   • After executing this code, you’ll have two collections:
     • CollBU: Contains the original data from the BusinessUnit data source with an additional column named “BU.”
     • CollFormattedData: Contains data from the Departments collection with “Values” and “Employee” columns.

We have to use CollFormattedData, it has the required formatted data as shown below:

Note: This is not a 100% accurate to get the required formatted data, but an alternate approach.

Please provide comments and suggestions.