Splitting just one column into multiple rows

(0) Share

Report

Posted on by desreed

Hi all. First time poster, so apologies in advance if I'm not following some etiquette!

I've got an issue with using the Split function. I have managed to get a collection that looks something like this:

Collection1

ID	Tags
1	Tag1;Tag2
2	Tag2;Tag3
3	Tag1
4	Tag4
5	Tag2;Tag4

What I'm hoping is to turn that into a collection that looks like this:

Collection2

ID	Tags
1	Tag1
1	Tag2
2	Tag2
2	Tag3
3	Tag1
4	Tag4
5	Tag2
5	Tag4

I can do this is seconds using PowerBI when I need to.

Basically I'm trying to use a few text fields to store the results of multi-value combo box to 'tag' records with certain info. I can do the patching of the data into the one text cell, but I'm struggling to split it back out to make it a usable dataset in the app.

Any help on this would be really gratefully received. Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (3)

Report
Here is an example where I first create your Collection1 as sample data then I create Collection2 by looping through Collection1. As the formula it loops through Collection1 it also splits the tags and loops through them and then adds a row to Collection2.

You should be able to copy and paste this example into a button and run it to see if it is giving you the results you need.

ClearCollect(Collection1, {ID:1, Tags:"Tag1;Tag2"}, {ID:2, Tags:"Tag2;Tag3"}, {ID:3, Tags:"Tag1"}, {ID:4, Tags:"Tag4"}, {ID:5, Tags:"Tag2;Tag4"}); Clear(Collection2); ForAll(Collection1, ForAll(Split(Tags,";"),Collect(Collection2,{ID:ID, Tags:Result})));

Was this reply helpful? Yes No
desreed 26 on at

Like (0)

Report

@Jeff_Thorpe - Thank you! I did as you suggested and pasted it in and got it working. I've also figured out how to adjust the final bit of your code to attach it to existing collections and connections and Ive got what I was looking for. Really great.

I can't pretend I understand that last bit of code though:

ForAll(Collection1, ForAll(Split(Tags,";"),Collect(Collection2,{ID:ID, Tags:Result})))

But, I've got it working, and it does exactly what I need now. So thank you very much, particularly for the quick answer!

Was this reply helpful? Yes No
Kumudha 24 on at

Like (0)

Report

Hello @Jeff_Thorpe ,
I have similar scenario with multiple columns. Can you please help me with that.

Screenshot 2021-0...

Was this reply helpful? Yes No
Anonymous_Hippo 1,247 Super User 2024 Season 1 on at

Like (0)

Report

@Kumudha were you able to figure out how to do this when you had multiple columns ? because i am trying to figure out the same

====================

Update: I did exactly what @Jeff_Thorpe showed and it works flawlessly. For some weird reason it showed some weird error when i tried first- but when i restarted powerapps boom !! it works 🙂

Many thanks @Jeff_Thorpe

Was this reply helpful? Yes No