pie chart extracted values

[jthake]

I have a dataset where each row is a survey response. In one of the columns, it is an answer to a question. Which the users can select more than one option, that is currently sepated by a semi-column (using the query editor).

What I'm trying to do is show a pie chart of the answers, and its treating the answers with multi-values as a unique answer, rather than splitting and counting as distinct individual values on the pie chart.

Is there a way to handle this?

e..g.

SurveyID | Answer

1 | Red; Yellow

2 | Yellow

3| Green; Red

4 | Yellow

The pie chart would show: Red = 2, Yellow = 3, Green = 1

[v-sihou-msft]

@jthake

Pie chart will always aggregate the data on category level. So when you put Answer into Pie chart, it will count the number of Answers for different colors.

In your scenario, if you want to analysis data on each survey level, it's better to use other type of chart, like Column chart. You can put Survey on X-axis, and put Answer on Legend. Then use Count of Answers as Value.

Regards,

[leJuan5150]

One of many options is you can right-click on the "Answer" column in the Query Editor and "Split Column -> By Delimiter...".  In the "Advanced Options" split into "Rows".  Make sure you Trim the column to remove the leading spaces.  The resulting dataset should work well with the pie chart.

[jthake]

Split column is disabled on this column. Right now each row redners in Query Editor as "[List]". 
I can extract values and do as 'colon'. Then Ican split. But it creates a new column with ".1" and ".2".  I'm guessing if there are more mutliple selections you'd get more of these too.

Then when I try to use both columns in the pie chart. I drag both columns onto legend  and both as counts onto values. but as not all columns have values, it shows a count on some blank. If I try an do an advanced filter. ANd "is not blank" it removes rows from pie chart. 

How do I do this properly so it shows all?

[leJuan5150 · Accepted solution]

When you do the split, in the advanced options you should be able to split into rows:

The result should look like:

Now if you create a pie chart it should give you what you are looking for: