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

All responses (4)

Answers (1)

Verified answer

timl 35,193 Super User 2025 Season 1 on at

Like (0)

Report
Re: Build Collection from column with colon delimiter

@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")
kccchiu 12 on at

Like (0)

Report

Re: Build Collection from column with colon delimiter

Yes, it is a 2 column table with header like you mentioned. Sorry for the confusion.
mdevaney 29,987 Super User 2025 Season 1 on at

Like (1)

Report
Re: Build Collection from column with colon delimiter

@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."
timl 35,193 Super User 2025 Season 1 on at

Like (0)

Report

Re: Build Collection from column with colon delimiter

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?