Forum Discussion
Helper IComplete newbie! How to organize data from a complicated SharePoint list
Hi I have a very wide sharepoint list that I need to organize. It looks a bit like this:
| ID | l1_total | l1_project | l1_activity | l1_description | l2_total | l2_project | l2_activity | l2_description | Receipt # | Attachements | Supplier |
| 1 | 20$ | Project 1 | Activity 1 | Description1 | 10$ | Project 2 | Activity 2 | Description2 | 1234 | Attachement 1 Attachement2 Attachement 3 | Supplier1 |
| 2 | 100$ | Project 3 | Activity 1 | Description3 | 123$ | Project 1 | Activity 1 | Description 4 | 1235 | null | Supplier2 |
| 3 | 25$ | Project 1 | Activity 2 | Description5 | null | null | null | null | null | null | Supplier3 |
How can I manipulate the data to build a report that might look like this
| ID | Total | Project | Activity | Description | Receipt# | Attachements | Supplier |
| 1 | 20$ | Project 1 | Activity 1 | Description1 | 1234 | Attachements 1,2 and 3 | Supplier1 |
| 1 | 10$ | Project 2 | Activity 2 | Description2 | 1234 | Attachements 1,2 and 3 | Supplier1 |
| 2 | 100$ | Project 3 | Activity 1 | Description 3 | 1235 | null | Supplier2 |
| 2 | 123$ | Project 1 | Activity 1 | Description 4 | 1235 | null | Supplier2 |
| 3 | 25$ | Project 1 | Activity 2 | Description5 | null | null | Supplier3 |
My table is actually alot bigger with 10 project, activity, total, description (and more) collumns. I tried to unpivot, but when I unpivot all those collumns, i only get all the info for one ID. All the others dissapear. And, the new lines created by the unpivots don't seem to line up. When I unpivot the second set of collumns, I get double the lines, and then quadruple, etc.... I'm surely doing something wrong.
4 Replies
- Greg_Deckler
Community Champion
JoelDucharme Create 10 queries that each pull only the fields required for each Project group of columns plus the Receipt, #Attachments and Supplier columns. Set them all to not load and then create a query that appends them all together.
JoelDucharmeThanks for your help. I created all my queries, but how do I append them all together? When I use the "append queries as new", I basically just get the original list.
- Greg_Deckler
- JoelDucharme
Thanks! That's what I had done and it didn't seem right. I looked at it further and it's what I need. The steps that were missing for me though is I had to combine the differents line collumns. For example, I combined l1_project, l2_project, l3_project, etc... to make one "Project" collumn. After that though, I had a whole bunch of entries that were empty, except for the ID collumn. To remedy that, I made a new combined collumn, deleted the empty ones and then deleted this new collumn to keep it clean.
All of this worked. Thanks for your help.