extract multiple names from 1 cell

[whitneyanne]

I have a report that I pull for safety events. Sometimes the events are reviewed by multiple people. When I pull the report from the system it lumps all reviewers together in a cell. Each reviewer is from different services. I am to create a report that shows how many reviews were completed by each reviewer without having to do a lot of manual work in Excel before importing to PowerBI. 

Does anyone have any experience with this?

[bolfri · Accepted solution]

Let's assume that you have such data:

EventName	ReviewersString
Christmas	Johnson, Reviewer Guy / Anderson, Reviewer Gal / Random, Reviewer Person
Halloween	Johnson, Reviewer Guy / Anderson, Reviewer Gal
Dummy Summer Event	Anderson, Reviewer Gal
Dummy Event	John, Snow

Step 1. Split ReviewersString by " / " into rows.

Create a measure and use it 🙂

Number of reviews = COUNTROWS(RevTable)

[whitneyanne]

Also, I have over 180 reviewers... so it can be 1 person, or multiples and not always the same names. 😕

[whitneyanne]

Johnson, Reviewer Guy / Anderson, Reviewer Gal / Random, Reviewer Person

[Ashish_Mathur]

Hi,

In the Query Editor, select that column and use the split Column > By delimiter.  In Advanced Options, select Rows.

[kpost]

Get the number of "/" and +1 to it to get the total number of reviewers.

Here's someone with a similar question, but they needed the number of hyphens.

https://community.fabric.microsoft.com/t5/Desktop/How-to-count-a-specify-letter-in-a-string-in-power...

so add a new column like this in Power Query:

List.Count(Text.PositionOf([Reviewers],"/",Occurrence.All )) + 1

(This assumes there is always at least 1).  If that's not the case, you will need additional steps (perhaps an intermediary step with a new column, or another column after this with IF/THEN logic, or more complex power query statement to begin with) to account for the situation where the [Reviewer] field is totally blank and should be zero

[whitneyanne]

Hi thank you,

But I am looking for how many each reviewer completed. Not how many reviewers reviewed a certain event.

[kpost]

https://community.fabric.microsoft.com/t5/Power-Query/Column-with-comma-separated-values-to-list/td-...

Could you follow this post except use "/" instead of comma then remove trailing and leading white space?

Then add a column with the value "1" and summarize it by name in your visual?

[DOLEARY85]

Hi, do you have any example data to show how the names appear together in a cell?

[whitneyanne]

Johnson, Reviewer Guy / Anderson, Reviewer Gal / Random, Reviewer Person