cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oskarkuus
Post Prodigy
Post Prodigy

String to table - how can i split a string into multiple columns in a table?

I have a string that looks like this

 

urlaa;urlbb;urlcc;nameaa;namebb;namecc

 

i want to split this string into a table with two columns, URL and NAME column.

URL | NAME

urlaa | nameaa

urlbb | namebb

urlcc | namecc

i know about the split function but it just creates one column.

i tried to create two collection but have not been successful in merging them ...

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Oskarkuus 

Well, the question to start with is where you wanted to use the Formula!!

That formula will return a table.  So any place you want that table to be, it will work.  So, if you wanted this in a gallery, then you would put it in the Items property.  If you wanted it as a dropdown or combobox, you would put it in the Items property.

 

So, not sure where you wanted it to start with - perhaps you can clarify that?

 

As for the formula itself, it is pretty basic...

- first it splits the text into a table and holds that resulting table in the _items variable.

- then a ForAll is used (which is a function that returns a table - it is not a For Loop like in some development language) to create a table.

- The record schema of the table is defined in the record specified to the ForAll.  That schema is an URL column and a NAME column.

- The iterator for the ForAll is a single column table that is returned from the Sequence function.  The sequence function is just a sequential table of numbers from 1 to the count of all the split values. (see below)

- All of that then ultimately just returns a table with the columns as you were looking for.

 

I noticed I neglected one important part of the formula.  I forgot to specify the Step parameter of the Sequence function. As well there was a syntax error.

The formula revised and accurate is this:

With({_items: Split("urlaa;urlbb;urlcc;nameaa;namebb;namecc", ";")},
    ForAll(
        Sequence(CountRows(_items) / 2, 1, 2),
            {URL: Last(FirstN(_items, Value)).Result,
             NAME: Last(FirstN(_items, Value + 1)).Result
            }
    )
)

 

So again, throw this in an Items property for a Gallery, for example, and add some labels to the gallery to display the column values you want and you will see the results.

RandyHayes_0-1647549101895.png

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

10 REPLIES 10
RandyHayes
Super User
Super User

@Oskarkuus 

Consider the following formula for what you need:

With({_items: Split("urlaa;urlbb;urlcc;nameaa;namebb;namecc", ";")},
    ForAll(
        Sequence(CountRows(_items),
            {URL: Last(FirstN(_items, Value)).Result,
             NAME: Last(FirstN(_items, Value + 1)).Result
            }
        )
    )
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks for a fast response.

But i dont really get the code and where to use it and what is what in it.

Do you mind explaining a bit more?

RandyHayes
Super User
Super User

@Oskarkuus 

Well, the question to start with is where you wanted to use the Formula!!

That formula will return a table.  So any place you want that table to be, it will work.  So, if you wanted this in a gallery, then you would put it in the Items property.  If you wanted it as a dropdown or combobox, you would put it in the Items property.

 

So, not sure where you wanted it to start with - perhaps you can clarify that?

 

As for the formula itself, it is pretty basic...

- first it splits the text into a table and holds that resulting table in the _items variable.

- then a ForAll is used (which is a function that returns a table - it is not a For Loop like in some development language) to create a table.

- The record schema of the table is defined in the record specified to the ForAll.  That schema is an URL column and a NAME column.

- The iterator for the ForAll is a single column table that is returned from the Sequence function.  The sequence function is just a sequential table of numbers from 1 to the count of all the split values. (see below)

- All of that then ultimately just returns a table with the columns as you were looking for.

 

I noticed I neglected one important part of the formula.  I forgot to specify the Step parameter of the Sequence function. As well there was a syntax error.

The formula revised and accurate is this:

With({_items: Split("urlaa;urlbb;urlcc;nameaa;namebb;namecc", ";")},
    ForAll(
        Sequence(CountRows(_items) / 2, 1, 2),
            {URL: Last(FirstN(_items, Value)).Result,
             NAME: Last(FirstN(_items, Value + 1)).Result
            }
    )
)

 

So again, throw this in an Items property for a Gallery, for example, and add some labels to the gallery to display the column values you want and you will see the results.

RandyHayes_0-1647549101895.png

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Ok now i get an result and start to understand it.

but i get the same result you show in your picture.

Oskarkuus_0-1647549793749.png

That is not the result in the table im after

Oskarkuus_1-1647549816965.png

 

In my example i want those that start with url to be in one column and those with name in the other.

 

now in the example namebb is in the url column.

 

RandyHayes
Super User
Super User

@Oskarkuus 

Ah yes, I see in your string now the difference.

 

Okay...so this begs the question - in your example, you have three URL records to start with then followed by three NAME values for those URL's.

How will you determine how many URL items to get from the string?

 

Example, if your text is url-1;url-2;url-3;url-4;name-1;name-2;name-3;name-4

How will you know that there should be 4 pulled and not 3 like the other text?

There has to be something to distinguish it.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

well the idea of the whole thing is that i let the user add records to a collection in my app.

they add the url and they add the name and press save and a row is created in the collection.

i dont know how long (many rows) the collection will be, depends on the user.

 

so then instead of saving the collection to a table in dataverse i want to save it as a string in the main table.
i would save it by doing concat(collection;column1) & concat(collection;column2)

so the order of the data would be the same always.

 

then when user opens the app to read the data i want to put the string back into a collection.

 

Oskarkuus
Post Prodigy
Post Prodigy

maybe this is a bad idea and i should create a child table instead for this...

RandyHayes
Super User
Super User

@Oskarkuus 

Not a bad idea, just a bad idea on how you are saving it as you'll never know when pulling it back as a string how many are url's and how many are names.

I would suggest saving to the column in your datasource with this formula instead:

Concat(collection; column1 & ";" & column2 & ";")

This way it will be the first is the url and the second is the name for as many items that there are.

Then the formula I provided will work fine and you can have any number of pairs in your text string.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Great, that worked directly.

Thank you very much for sticking around and finding the error.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,688)