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

[Oskarkuus]

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 ...

[RandyHayes]

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

[Oskarkuus]

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 · Accepted solution]

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

[Oskarkuus]

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

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

That is not the result in the table im after

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]

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

[Oskarkuus]

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]

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

[RandyHayes]

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

[Oskarkuus]

Great, that worked directly.

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