Multiple Response Question: Count responses stored in a single column, separated by a space

[AlexMic]

Hello community.. please your help..

From a multiple question in a quiz, a single column was generated that has all responses separated by a space. I want to build a bar graph that shows the number that appears for each answer and the % vs. the total number of respondents. In my file I have 1000 rows, one row for each respondent, and of course other questions so I don't want to impact the structure of the file.
Thanks for your suggestions.

[V-lianl-msft · Accepted solution]

Hi @AlexMic ,

If you do not have user name, add an index column to the original file. This can be used as a count of the total number of people.

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

Then create a measure like below:

Measure = 
var total_ =CALCULATE(DISTINCTCOUNT('Table (2)'[Index]),ALL('Table (2)'))
var current_row = COUNT('Table (2)'[Index])
return DIVIDE(current_row,total_)

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

[AlexMic]

thanks... it's the first step... but how do I calculate the % with respect to the number of people surveyed (number of rows in the original file).. no versus the number of responses? Thank you.

[Ashish_Mathur]

Hi,

You can use the DISTINCTCOUNT() function.

[Ashish_Mathur]

Hi,

You will have to split the answers by rows so that each answer is in its own cell.  This can easily be done in the Query Editor.  If you need further help, share the download link of your PBI file.

[Syndicate_Admin]

Good morning, I have the same problem with comma separator. 😕