Need Help on splitting/combining values

[Alex_Belfiglio]

I'm having a table of people, each with a value of how many projects they are working on. But the list contains duplicates when two people are working on it together, which is counted seperately. 

Ex: There are 3 people a 10 total projects, here is how the list would look.

"Person 1" - 3 projects

"Person 2" - 2 Projects

"Person 3" - 2 Projects

"Person 1 / Person 2" - 2 Projects

"Person 2 / Person 3" - 1 Project

Is there a way to I can split or combine these values, or insert a delimeter of "/" to get a cleaner listing like this?

"Person 1" - 5 Projects

"Person 2" - 5 Projects

"Person 3" - 3 Projects 

[parry2k]

are quotes (") part of your data or it is just put in sample.

[Alex_Belfiglio]

Just a sample to show that Person 1 / Person 2 is together.

[parry2k]

can you share how your real data looks like?

Also do you always maximum 2 person like Person 1 / Person 2 or it can be more than 2 as well?

[Alex_Belfiglio]

Sure, here is the table I'm working on in Direct Query.

Here are the charts that I'm trying to work with, with the top chart showing what I get, and the bottom filtered to show what I'm trying to make (with incorrect counts). But having trouble getting the values to correctly allocate to the individual persons.

[parry2k · Accepted solution]

ok i think it is easy :

- goto query editor

- select "ASsigned Person" column

- under "Split Column" in menu, choose "By Delimiter"

- Choose semicolor delimiter

-  Click Advaced option and select "rows"

- click ok

and this will do, so in you graph, now you will get the data what you are looking for

- drop person on x axis and project id on value (and choose count as summarization)

[Alex_Belfiglio]

Thanks, I think I'm getting slightly closer. I don't seem to have a "rows" option, this might due to not having the last couple updates.

It did split well into additional columns, I'm hopin to maybe add these together on my chart axis, but it looks like right now it is still filtering by the hierarchy.

[parry2k]

i think you missed one step there , you need to choose Rows in advanced than columns

[parry2k]

this is what i produced from you data, assuming there / delimiter where more than 1 people working on a project.