cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
desreed
Frequent Visitor

Splitting just one column into multiple rows

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

IDTags
1Tag1;Tag2
2Tag2;Tag3
3Tag1
4Tag4
5Tag2;Tag4

 

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

 

Collection2

IDTags
1Tag1
1Tag2
2Tag2
2Tag3
3Tag1
4Tag4
5Tag2
5Tag4

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Thorpe
Super User
Super User

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})));


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

3 REPLIES 3
Jeff_Thorpe
Super User
Super User

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})));


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

@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!

 

Hello @Jeff_Thorpe ,

I have similar scenario with multiple columns. Can you please help me with that.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,080)