Build Collection from column with colon delimiter

(0) Share

Report

Posted on by kccchiu

Hello,

I have a table that look like this:

Name Pets
Tom ;Cat;Dog
Peter ;Bird;Rabbit
Mary ;Hamster

and would like to get a collection from the Pets column:

Cat
Dog
Bird
Rabbit
Hamster

I have tried using ForAll and Split but still no luck. Would appreciate if someone can help me with this.

Thanks,

Kin

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

timl 36,411 Super User 2025 Season 2 on at

Like (0)

Report

Hi Kin

Can you confirm, is this a table with two columns called name and pets, or is this a single column table where each data row begins with the name followed by the name of the pets separated by a semicolon?

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (1)

Report
@kccchiu

This code will produce a single column collection with a column called "Result" having all the pets.

// Starting collection ClearCollect(colNamePets, {'Name Pets': "Tom;Cat;Dog"}, {'Name Pets': "Peter;Bird;Rabbit"}, {'Name Pets': "Mary;Hamster"} ); // Solution collection ClearCollect(colSolution, Ungroup( AddColumns( colNamePets, "Pets", Remove(Split('Name Pets', ";"),First(Split('Name Pets', ";"))) ), "Pets" ).Result );

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
kccchiu 12 on at

Like (0)

Report

Yes, it is a 2 column table with header like you mentioned. Sorry for the confusion.

Was this reply helpful? Yes No
Verified answer

timl 36,411 Super User 2025 Season 2 on at

Like (0)

Report
@kccchiu

Let's assume a 2 column table that looks like this:

ClearCollect(colNamePets, {'Name': "Tom", 'Pets': "Cat;Dog"}, {'Name': "Peter", 'Pets': "Bird;Rabbit"}, {'Name': "Mary", 'Pets': "Bird;Rabbit"} )

This formula will return a table with a column called Result. This will contain a list of your pets.

Ungroup(AddColumns(colNamePets, "PetTable", Split(Pets, ";")),"PetTable")

Was this reply helpful? Yes No