Forum Discussion
Sharepoint/Data Manipulation
I am making a report connected to a Sharepoint List. There are multiple columns in it out of which two are relevant.
There is the Title and then there is the Team. People add in names in that column (is atleast one, could be more, usually 3-5). In the report I would like to see which member was in which project; which project had which members, and the number of projects a given member had participated in.
The two columns below represent how I get the data in. I can split the "Team" column however since the data is the same, i can't figure out a smart way of modelling this.
One tedious way could be that making separated queries out of each projects but that would mean around a 100 queries and this is a live connection to Sharepoint.
| Title | Team |
| Project 1 | Member1; Member2; Member3 |
| Project 2 | Member2; Member1; Member 4 |
| Project 3 | Member5; Member1; Member 3 |
Thanks for the help
You can do this in Power Query. Select the team column and then hit 'Split Column by delimiter'. This will split the team columns in multiple columns. Then select all new columns and hit 'Unpivot Columns', then filter out the blanks of the newly created Value column (there won't be blanks in the example data you gave, but I am guessing not every project has an equally large team) and now you have a one to one mapping between project name and team member. So if you have 3 teammembers in projectA, then you now have 3 rows with ProjectA and corresponding teammember name.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
2 Replies
- JarroVGIT
Resident Rockstar
You can do this in Power Query. Select the team column and then hit 'Split Column by delimiter'. This will split the team columns in multiple columns. Then select all new columns and hit 'Unpivot Columns', then filter out the blanks of the newly created Value column (there won't be blanks in the example data you gave, but I am guessing not every project has an equally large team) and now you have a one to one mapping between project name and team member. So if you have 3 teammembers in projectA, then you now have 3 rows with ProjectA and corresponding teammember name.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂