Making a Collection from a String

[CChapman]

I'm new to PowerApps and could use a helping hand with this.
I am getting a string from SharePoint, and then need to split it into a collection. The string has several pieces of data in it:
A user's email address,
A user's status (pending, approved, or rejected)
A user's role (CEO, Manager, or Employee)
And the "step" a user is associated with (any number, 1-100)

Each users's pieces of data (email, status, role, step) are separated by commas (and there is unfortunately a trailing comma left at the end of each user's last piece of data). Each user is separated from the next user by a space. An example of the way the string might look is this:

MRay@gmail.com,rejected,CEO,1, CSmith@gmail.com,approved,Manager,2 DMartin@gmail.com,pending,Manager,2 EGrant@gmail.com,pending,Employee,3,

I need split this string into a collection that has properties like this, for each user:
{ Email: userEmail, Status: userStatus, Role: userRole, Step: userStep }

So the collection would be:

Email                         |      Status      |        Role         |     Step
MRay@gmail.com       rejected                CEO                  1
CSmith@gmail.com     approved             Manager           2
DMartin@gmail.com   pending                Manager          2
EGrant@gmail.com      pending               Employee         3


Ultimately, the goal is to use that collection as the "items" in a gallery, so I can display all that info above to the user, but I know how to get the values of a collection into a gallery. If there's maybe some easier way to get this info into a gallery (instead of splitting it into pieces and getting those into a collection) I'd love to hear about it.

I've tried about a hundred combinations of using "split," "concat," "first," "ForAll" and etc. None of them were successful enough to be worth repeating here. The one thing I can do is split the string into a collection with each user's data housed inside one index (what do we call them in PowerApps since we don't have indexes??) like this:
ClearCollect( workflow, Split( sharepointString," " ) );   // sharepointString being the string name


Any advice on how to wrangle this would be much appreciated!

[v-bofeng-msft · Accepted solution]

Hi @CChapman :

Do you want to convert the following string into a table?

MRay@gmail.com,rejected,CEO,1, CSmith@gmail.com,approved,Manager,2 DMartin@gmail.com,pending,Manager,2 EGrant@gmail.com,pending,Employee,3,

The key is to use the split function in a nested way (using "," and "" as separators)

I've made a test for your reference:

1\Add a button control and set it's OnSelect property to:

ClearCollect(
    thecollection1,  /*my custom collection*/
    ForAll(
        ForAll(
            Split(
                "MRay@gmail.com,rejected,CEO,1, CSmith@gmail.com,approved,Manager,2 DMartin@gmail.com,pending,Manager,2 EGrant@gmail.com,pending,Employee,3,",
                " "
            ),
            Split(
                Result,
                ","
            )
        ),
        {
            Email: First(Value).Result,
            Status: Last(
                FirstN(
                    Value,
                    2
                )
            ).Result,
            Role: Last(
                FirstN(
                    Value,
                    3
                )
            ).Result,
            Step: Last(
                FirstN(
                    Value,
                    4
                )
            ).Result
        }
    )
)

Best Regards,

Bof

[CChapman]

Thank you @v-bofeng-msft!!

I spent several hours (under a very tight deadline on this project) trying to get this to work, so I am so grateful for your help with this. I also learned a ton about "ForAll" and ForN (which I wasn't aware of). Thank you again!