Answered

Creating a delimited Collection from another Collection

(0) Share

Report

Posted on by XenDance

Hi Power Apps Community!

I have created a Collection in a PowerApp which pulls data from a SharePoint list column which has been stored in a delimited format (example below):

Members

1;John Doe;John.Doe@examplemail.com;1000001;1000;2;David Doe;David.Doe@examplemail.com;1000002;1500

1;Sarah Doe;Sarah.Doe@examplemail.com;1000003;500;2;John Doe;John.Doe@examplemail.com;1000001;2000;3;Alfred Doe;Alfred.Doe@examplemail.com;1000004;2500

1;Steve Doe;Steve.Doe@examplemail.com;1000005;5000;2;Paul Doe;Paul.Doe@examplemail.com;1000006;2000;3;Alfred Doe;Alfred.Doe@examplemail.com;1000004;3000

The data stored in each line of the Collection always has 5 fields (separated by ';') per individual and can contain up to 10 individuals (so would be repeated 10 times giving a total of 50 fields) per row. There are no blanks to be concerned about.

I am looking at converting all rows in this collection into a new Collection in the following format:

Serial	Name	Email	StaffID	Value
1	John Doe	John.Doe@examplemail.com	1000001	1000
2	David Doe	David.Doe@examplemail.com	1000002	1500
1	Sarah Doe	Sarah.Doe@examplemail.com	1000003	500
2	John Doe	John.Doe@examplemail.com	1000001	2000
3	Alfred Doe	Alfred.Doe@examplemail.com	1000004	2500
1	Steve Doe	Steve.Doe@examplemail.com	1000005	5000
2	Paul Doe	Paul.Doe@examplemail.com	1000006	2000
3	Alfred Doe	Alfred.Doe@examplemail.com	1000004	3000

Is it possible to achieve this using PowerApps?

Cheers!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Michael E. Gernaey 53,968 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @XenDance

Real fast, is there a carriage return at the end of each line? Or if it just all one big string with delimiters? Just want to validate before I write the full answer.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
mmbr1606 14,629 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey @XenDance

Can you try something like this please:

ClearCollect(NewCollection); // Initialize the new collection to store the transformed data

ForAll(

OriginalCollection, // Assuming 'OriginalCollection' is your original collection name

ForAll(

Sequence(RoundDown(Len(Members)/10, 0)), // Calculate how many members are in each row (assuming each has 5 fields)

Collect(

NewCollection,

{

Serial: Value(Mid(Members, (Value-1)*50+1, 1)), // Extract Serial, adjusting indexes appropriately

Name: Mid(Members, (Value-1)*50+3, Find(";", Members, (Value-1)*50+3)-(Value-1)*50-3), // Extract Name

Email: Mid(Members, Find(";", Members, (Value-1)*50+3)+1, Find(";", Members, Find(";", Members, (Value-1)*50+3)+1)-(Find(";", Members, (Value-1)*50+3)+1)), // Extract Email

StaffID: Value(Mid(Members, Find(";", Members, Find(";", Members, (Value-1)*50+3)+1)+1, Find(";", Members, Find(";", Members, Find(";", Members, (Value-1)*50+3)+1)+1)-(Find(";", Members, Find(";", Members, (Value-1)*50+3)+1)+1))), // Extract StaffID

Value: Value(Mid(Members, Find(";", Members, Find(";", Members, Find(";", Members, (Value-1)*50+3)+1)+1)+1, 5)) // Extract Value

}

)

)

);

Be aware that you might need to rename a few things

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @XenDance ,

I made a sample for you.

ClearCollect(Col, {Members:"1;John Doe;John.Doe@examplemail.com;1000001;1000;2;David Doe;David.Doe@examplemail.com;1000002;1500"}, {Members:"1;Sarah Doe;Sarah.Doe@examplemail.com;1000003;500;2;John Doe;John.Doe@examplemail.com;1000001;2000;3;Alfred Doe;Alfred.Doe@examplemail.com;1000004;2500"}, {Members:"1;Steve Doe;Steve.Doe@examplemail.com;1000005;5000;2;Paul Doe;Paul.Doe@examplemail.com;1000006;2000;3;Alfred Doe;Alfred.Doe@examplemail.com;1000004;3000"} )
ClearCollect(Col2,Ungroup(ForAll(Col,With({temp:Split(Members,";")},ForAll(Sequence(CountRows(temp)/5,0),{Serial:Index(temp,Value*5+1).Value,Name:Index(temp,Value*5+2).Value,Email:Index(temp,Value*5+3).Value,StaffID:Index(temp,Value*5+4).Value,Value:Index(temp,Value*5+5).Value}))),"Value"))

Best Regards,

Wearsky

Was this reply helpful? Yes No
XenDance 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your responses! 🙂

@mmbr1606, unfortunately I was unable to get your answer to work with my collection - it provided me with 20+ results and split the strings the wrong length (e.g. john.do;e@examplemail.com) into different columns.

@v-xiaochen-msft, this worked perfectly. Thank you for this solution!

Was this reply helpful? Yes No