cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kumudha
Regular Visitor

Split data in cell into rows for multiple columns

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. 

 

Screenshot 2021-08-16 150608.png 

4 REPLIES 4
zmansuri
Super User
Super User

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

---------------------------------------------------------------------------------------------------------------------------


Was I helpful? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. It will help other users to find it faster.

follow me on Twitter @zmansuri123,
connect with me on LinkedIn from Here

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
Super User
Super User

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)

rubin_boer_0-1629364950312.png

 

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

rubin_boer_1-1629365126990.png

 

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

rubin_boer_2-1629365268473.png

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

rubin_boer_3-1629365763053.png

 

 

 

 

 

 

 

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

Hi @rubin_boer ,

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,473)