Separate names from column into different rows- to count them in chart

[NaddingGod]

So i have a column:

when i go plot this i get something like:

I need a to able to seperate these names and have a graph with individual names instead of Name1/name2 in the charts.

[AllisonKennedy]

@NaddingGod You can do this easily in Power Query - do you have access to Power Query in your dataset?

Click Home tab > Transform Data: https://docs.microsoft.com/en-us/power-query/split-columns-delimiter 

[NaddingGod]

Yes, I can separate this using delimiters, but I want them as an entry in each person's name when I plot the chart

Suppose my data looks like:

Responsible |  count

Name1.   | 1

Name1/name 2. | 1

Name 2/name3. | 1

So when I go plot the chart I wanna see the following.

Name 1 - 2

Name 2 - 2

Name 3 -1

[v-kelly-msft]

Hi  @NaddingGod ,

Using below M codes to transform your data:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTXUO7RA4dCCGgVDpVgdqJB+HpBUMNJTQBZVMAILG0NFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Responsible |  count" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Responsible |  count", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Responsible |  count", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Responsible |  count.1", "Responsible |  count.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Responsible |  count.1", type text}, {"Responsible |  count.2", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Responsible |  count.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Responsible |  count.1.1", "Responsible |  count.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Responsible |  count.1.1", type text}, {"Responsible |  count.1.2", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Responsible |  count.2"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Remove([Value],".")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Remove([Custom]," ")),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom1",{{"Custom.1", Text.Trim, type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text",{{"Custom.1", Text.Proper, type text}}),
#"Grouped Rows" = Table.Group(#"Capitalized Each Word", {"Custom.1"}, {{"Count", each List.Sum([#"Responsible |  count.2"]), type nullable number}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each [Custom.1] & "-"&Text.From([Count]))
in
#"Added Custom2"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[AllisonKennedy]

@NaddingGod  Are you still needing help on this?

@v-kelly-msft  your suggestion seems unnecessarily long in Power Query - you can let the visual do the Count and grouping for us. Also shouldn't need any unpivoting if you use my suggestion to split column into rows: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lEyVIrVgfL0QaQRipgRWMwYIhYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Responsible = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Responsible", type text}, {"Count", Int64.Type}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Responsible", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Responsible"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Responsible", type text}})

Result is: 

Then use in a table visual with SUM of Count: 

in
#"Changed Type1"

[v-kelly-msft]

Hi  @AllisonKennedy ,

Thanks for your advise.

Yes,splitting into rows is enough.If the data is small,unpivotting is also a choice,that's why I present my above method.

Hi @NaddingGod ,

I found your oringinal data has some uppercase problem.In my method,I adjust them during the transformation and get the result I attached before.You may check my .pbix file for reference.

If your issue is solved,could you pls mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

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

[AllisonKennedy · Accepted solution]

@NaddingGod Yes, so after you split the Name by Delimiter, use the new column with just the Name (part before the /) in the chart and then put the Count column in values - that will automatically group for you.

You'll want to split into ROWS: https://www.excelcampus.com/powerquery/split-into-rows/