Split data in cell into rows for multiple columns

[Kumudha]

I am working on a scenario where I have a SharePoint list and the data in a cell should be split into rows for multiple column and to be displayed in PowerApps.

When an new item or a column is added the items should be split into rows for all the columns and should be displayed in power apps dynamically with the new columns displayed dynamically. 

 

[zmansuri]

You can use "find" function to find where "|" is in your string.

Then use "Left" function and give output of the find as an input.

For ex.

Your string is Test|Final

Left(YourString,Find(YourString,"|")) This will give you your header. If your are using gallery you can do something like this:

Left(ThisItem.String,Find(ThisItem.String,"|"))

And Right function for opposite operation. Syntax maybe not exact. This is just the gist

[Kumudha]

Thank you for your replay .

I want that to be applied for each cell of SP list and if new columns are added in SP list it should be dynamically displayed in PowerApps gallery .

[rubin_boer]

hi @Kumudha here is some direction you can consider

• create a collection with header and data column
• group by the header column
• add a horizontal gallery to display all the headers
• Add a vertical gallery as sub gallery to the horizontal one and display the data belonging to it. 

Now a bit slower

lets generate test data to simulate your data.

adding a button to create a collection which will be your SharePoint List

button OnSelect = ClearCollect(colData,ForAll(Sequence(5,1,1),{string:"|Column heading|data"&Value}))

this is what you will see in a gallery with this collection assigned to it (looks like yours doesn't it)

now for the second part lets create a collection that split the column heading and data. i will do this with another button

Onselect = ClearCollect(colSplitData,ForAll(colData, {header: First(Split(Right(string,Len(string)-1),"|").Result).Result, data: Last(Split(string,"|").Result).Result}))

I am splitting each row by "|" data is the last part and header is the first part, ignoring the first "|". i now have a collection with two columns is it, header and data.

when adding this collection to a gallery, you will get this

almost there lets add a horizontal gallery ad set its Items = GroupBy(colSplitData,"header","Headers")

add a label to this gallery and set its Text = ThisItem.header

Add a gallery to this gallery, vertical gallery and set its Items = Filter(colSplitData,header = ThisItem.header)

this is what you will get

Hope this will put you on the direction you wish

PS,

this will only do th erows dynamically not columns. here is what happens when you have two header categories

[Kumudha]

Hi @rubin_boer ,

How can the same be done for data coming from SharePoint list with multiple columns.