Splitting up names and recording sum of count

[Anonymous]

Hi there,

I have a column of names in a table that looks like this: 

Joe Blogs

John Smith

Sarah Person; John Smith

Joe Blogs

I want to be able to graph the count of each person, such that:

Joe Blogs: 2

John Smith: 2

Sarah Person: 1

However I can't manage to separate it when there are two names, so it looks like this:

Joe Blogs: 2

John Smith: 1

Sarah Person; John Smith: 1

Sarah Person: 1

How might I be able to do this without creating a new column etc?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Go to query editor>split columns :

And you will see:

Then remove blanks:(using replace values)

Finally create a measure as below :

Measure = COUNT('Table'[Name])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , best id split the column in power query by rows

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/