Transform a column that shows multiple selections from a survey

Hello ToCa 

The easiest way to achieve that is to create a reference(or a copy) of your table in Power Query,

Then you only keep the unique ID and the response (in my case the color)

So you will have a table with two columns, after that you use Split the column by delimiter Comma (or the delimiter you need) and select for each occurence

You select all the column you get as result and select unpivot the columns

It will returns the following table:

Then you delete the table Attribute and now you have your dimension table and you can link it to the main table

If you need more support, do not hesistate for help 🙂

This is the code generated :

let
    Source = Table,
    #"Autres colonnes supprimées" = Table.SelectColumns(Source,{"Id", "Color"}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Autres colonnes supprimées", "Color", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Color.1", "Color.2", "Color.3"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"Id", Int64.Type}, {"Color.1", type text}, {"Color.2", type text}, {"Color.3", type text}}),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Type modifié", {"Id"}, "Attribut", "Valeur"),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Tableau croisé dynamique des colonnes supprimé",{"Attribut"})
in
    #"Colonnes supprimées1"