Multiple Values Same Column

[BPelzel]

We receive data from a survey that has a question with a list of values. People taking the survey can choose multiple values for the question.

When we receive that data in an Excel spreadsheet, it adds all their selections in one cell that are separated by a semi-colon. However, we want to produce a graph that shows the number of people who choose each, individual value.

Here is a fictional example:

How can we create a graph like this using multiple values in the same column?

[BPelzel]

Thank you all for your help! I had to add a couple of steps at the beginning to get started, but then followed your steps (especially the screenshot) to split the columns. In the end, these were the steps:

• Right click on the original table and choose Reference. This will create a new table.
• Highlight the column(s) you want to keep and right click on it. Choose to Remove Other Columns.
• Optional depending on if there’s space between options: Click Format option and choose Trim
• Click Split column option and choose Delimiter
  1. At each occurrence of the delimiter
  2. Rows (in advanced options)
  3. None for Quote Character
  4. OK
• Click on the Filter option for that column > Remove Empty

[v-angzheng-msft]

Hi, @BPelzel 

Glad to see that you found a solution. Since I don't have your sample file so I can only provide you with the key steps, It’s great that these steps are useful to you.
Thank you for sharing your complete steps, could you please mark the post which help as Answered since it is working now? It means a lot to me, and it will help the others in the community find the solution easily if they face the same problem with you.

Glad to help, thank you.

Best Regards,
Community Support Team _ Zeon Zheng

[v-angzheng-msft · Accepted solution]

Hi, @BPelzel 

1. Try to split cells into rows with commas and line breaks in PowerQuery.

2. And delete the extra spaces before and after the value.

Data:

Steps:

data:

Then to create a measure to count the fruit.

_count = COUNT('Table'[data])

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdthompson]

Hello, you need to tidy the data up in PowerQuery:

1. Split the first column by delimiter ","

2. Unpivot the new columns.

you will have a table that now has a field called attribute with all the fruits and one with the values.

3. Rename them and load back to PowerBi.

The code will be something like this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStTJV9JRMlWK1QGxk8A8IzAPwjaEyQDZxiiqzKE8ENtEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Value = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Fruit", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Fruit.1", "Fruit.2", "Fruit.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit.1", type text}, {"Fruit.2", type text}, {"Fruit.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value"}, "Attribute", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Fruit"}})
in
#"Renamed Columns"

[BPelzel]

Thank you for your quick reply!

Now I'm getting errors about 'Microsoft.Mashup.Evaluator.MessageBasedOutputStream+BinaaryChunkMessage' and that there's duplicate values for our employee ID that are not allowed in a many-to-one relationship. 

Also, I'm getting hung up on step 3. Now there are two columns at the end of the existing table for Attribute and Value. It appears that there's only one value (or null) in that Value column. How should I use those to create a table? Once that happens, will they go away to remove the error about duplicate employee ID's?