Splitting data with overlapping values and then collating into a chart

[briwhiz23]

Hi all. I have a Power BI dashboard that pulls in data from Airtable. In our tables (we have about 15), we have a "Responsible" field where you can tag one or more people. It pulls into Power BI like this, for example: 

You can see that "Brid" is included in rows with another person and also by themselves.

GOAL: We'd like to see each person's workload, so we need to split the names out, then collate them somehow.

When I pull the data as-is into a chart, it obviously doesn't separate the names by delimiter, and you can see where the overlap is (highlighted in green, and highlighted in yellow). Hence, we're unable to get a quick look at how much each person has on their plate.

When I delimit them and split them into separate columns, this is how it looks (not exactly better than the previous option). 

Any idea how I can separate these cells by delimiter and then re-combine the columns to get an accurate count of task by user without creating new rows? 

Thanks in advance for any help!

[PhilipTreacy · Accepted solution]

@briwhiz23 

You have to create new rows to do this.  Creating new rows will mean your data is in a tabular format which is what you need for analysis.

In Power Query when you split the names by delimiter, in the Advanced options choose to create new rows, rather than columns

Regards

Phil

[Anonymous · Accepted solution]

Thanks for the reply from PhilipTreacy , please allow me to provide another insight: 
Hi  @briwhiz23 ,

I created some data:

Here are the steps you can follow：

1. You can create a calculation table containing the data needed for the X-axis of the clustered column chart.

2. Create measure.

Measure =
var _vtable=
DISTINCT(
FILTER(
    CROSSJOIN(
        'Table','Group_Table'),
  FIND( 'Group_Table'[Group],'Table'[ASSIGEND TO],,BLANK()<>BLANK())))
return
COUNTX(
    FILTER(
        _vtable,[Group]=MAX('Group_Table'[Group])),[ASSIGEND TO])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PhilipTreacy · Accepted solution]

@briwhiz23 

You have to create new rows to do this.  Creating new rows will mean your data is in a tabular format which is what you need for analysis.

In Power Query when you split the names by delimiter, in the Advanced options choose to create new rows, rather than columns

Regards

Phil