cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edbellman
Advocate II
Advocate II

List column manipulation (columns to rows) into collection

Good morning, all.

 

I'm looking to create a collection from a SharePoint list, where the list columns equate to rows in the collection. Here is a simple example:

 

liscolumnmanipulation.png

 

I can easily create a collection that mirrors the SharePoint list (single row with multiple columns), but I haven't been able to figure out how to flip the data around so to speak to get the desired result show above.

 

As always, thanks in advance for ideas and assistance!

4 REPLIES 4
eka24
Super User
Super User

Do you expect the column names to be part of the output or converted to record as well?

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

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Consider Checking My Youtube Channel

 

edbellman
Advocate II
Advocate II

Yes, that is the goal.

 

I've been playing around with that piece a bit and am getting close with something like this:

Collect(colUserTrainingTEST,AddColumns('DU Module List'.TrackingName,"TrackingName2",TrackingName))
rubin_boer
Super User
Super User

hey hey @edbellman , this is one of those where you want to say "fix it at the data source" :).

 

You could do the following (not the cleanest tho but to give an idea).

 

Collect Bim and SharePoint in two collections and then add these two for e.g.

ClearCollect(colBim,ForAll(yourDatasource,{tracking:"BIM", value:BIM}));
ClearCollect(colSharePoint,ForAll(yourDatasource,{tracking:"SharePoint", value:SharePoint}));
ClearCollect(colTransposed,colBim,colSharePoint)

What you will get are these things

Bim collection

rubin_boer_0-1632232764387.png

 

SharePoint collection

 

rubin_boer_1-1632232783647.png

 

Combined

rubin_boer_2-1632232855941.png

 

Hope this gives some direction.

 

 

 

edbellman
Advocate II
Advocate II

@rubin_boer , thanks. Yeah I know changing the source is probably the best option, but this is an existing list with about 65-70 columns tracking attendance/completion of training classes for all employees, so it's what I have to work with atm.

 

That said, your solution appears to work but may not scale well (or easily at least) given the number of columns mentioned above but the end result is definitely what I need.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,129)