Best practice for splitting columns with multiple, comma separated values

[dphillips]

Just completed a survey with some students getting their feedback on wide reading. For 3 of the columns they were allowed to select multiple answers. See image below.

No problems spitting the columns into rows using the delimiter but, clearly, this will lead to many rows for each original row - eg row 1 would be 2 x 3 x 3. This then creates duplication when I am trying to do counts of these responses. For example, after splitting the first 2 columns we now have 6 rows of data and when I split the third column there will be 18 rows of data.

I simply would like to know the best way to shape my data to make getting accurate information out to teachers as easy and understandable as possible. Should I split the data into separate tables and link each table via studentid (which is one of the fields also provided in the survey) or do I keep it all in the one table etc etc. Can someone please tell me the best way to set up my data model. Thanks for any help.

[dphillips]

Thanks. I have followed these steps through keeping the dimension fields that I have in the able and unpivotting the rest. Makes sense and seems to be quite clear. Also created the dimension tables as you suggested and using these as filters. Just started creating some visuals. thanks so much for your help.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @dphillips , great question.  Can I suggest that you Unpivot questions into rows, then split:

From:

To:

Here is the example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUrUUUjSUUgGsip0FCp1FKqALEMdBSOlWJ1oJSdCCpwJKXDBqyAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, #"Question 1" = _t, #"Question 2" = _t, #"Question 3" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Student"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Value", Text.Trim, type text}})
in
    #"Trimmed Text"

I would add a dimension tables for Student and Questions.  You can use CALCULATE( exp , KEEPFILTERS( Question = 1 ) ) in your DAX functions.