Transform a column that shows multiple selections from a survey

[ToCa]

Hi,

What's the best way to transform the column below in power bi so that I can display a chart like the picture at the bottom?

For context this is a multiple selection question in microsoft forms where each row is a new response. 

Ferry;Airfare;Public Transportation costs (e.g. bus, taxi);Personal vehicle costs (e.g. gas);Parking;Unpaid time off from work;Accomodation and/or meals ;

Airfare;Personal vehicle costs (e.g. gas);Parking;Accomodation and/or meals ;

Ferry;Airfare;Public Transportation costs (e.g. bus, taxi);Parking;

Not applicable ;

Personal vehicle costs (e.g. gas);

Unpaid time off from work;Accomodation and/or meals ;

Not applicable ;

Personal vehicle costs (e.g. gas);Parking;Accomodation and/or meals ;

[Cookistador · Accepted solution]

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"

[GeraldGEmerick]

@ToCa This can be done fairly easily in Power Query. You can first Split by Delimeter (semi-colon) and then Unpivot all of the resulting columns. Then remove blanks if necessary. You can then place the Values column into the visual for you Y-Axis and then Count of Values column for the X-Axis:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZC9DoMwDIRfxWJqJUQfIBNLx4qhnRCDCQmNSOLICf15+yKoKnWioqt1vu/u6jo7KuanKA1rZCWqsbVGwpnRx0CcMBnyICmmCDtV9AW0Y8wh4cPsRaU4kkcLN3U10qovXY9xUiAPxvfi4gOaDpJxCkhr0EwO7sSDKKUkR93CQd8diMEptBFE1uR19gn2M2vN8Z/Cb8Tsc6IEGML0je2UZzmux5xl2/fYSP1pnOYF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Answers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Answers", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Answers", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Answers.1", "Answers.2", "Answers.3", "Answers.4", "Answers.5", "Answers.6", "Answers.7", "Answers.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Answers.1", type text}, {"Answers.2", type text}, {"Answers.3", type text}, {"Answers.4", type text}, {"Answers.5", type text}, {"Answers.6", type text}, {"Answers.7", type text}, {"Answers.8", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> ""))
in
    #"Filtered Rows"