Split data by comma

[NurAmirah_MY]

Hi,

I'm building a template dashboard for survey responses. There are multiple answer question where respondent can select more than answer. Hence, I want to show all the answers in chart. Please note that the responses data comes with , to seperate the answers. Eg: A, B, C, D

I have steps build up in power query. The steps are split the column by comma > unpivot selected column > the column can be used to chart the responses. Right now, the template has 4 columns from splitting up the response data (A | B | C | D). What if the survey has more than 4 multiple answers. When I refresh the power query that has data of 5 multiple answers, it didn't split up correctly instead it ignore. Eg: A, B, C, D, E and when split up the column it only shows until D. 

how to solve this? Thanks.

[NurAmirah_MY]

Hi @Fowmy 

Thanks for this solution. Will try in a bit.  Can I know when the power query run the split by column, if I want those columns after splitting to be unpivot, how can I do that?

[Fowmy]

@NurAmirah_MY 

Wihout having a sample data set, It's hard to assume and share a solution. 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[Fowmy]

@NurAmirah_MY 

You can split into ROWS under advanced:

Paste this code in a Blank Query and check the steps, add, more responses e,f,g

let

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