Multiple answer analysis

[lodwong]

I need help in pivoting the about data set which came as a result of respondents giving multiple responses.

I am anticipating the graph to be like the one below but am getting challenging with bringing data under column of sesame, Sunflower, Soybean and poultry together for ease comparison.

 

 

[Vijay_A_Verma]

In terms of data, do you want sum of those 4 columns against those rows and then summarize on column1?

[lodwong]

Yes! I want the sum of the 4 columns against the rows and then summarise on column1 but in away that it won't increase the numbers of rows. I tried unpivoting the 4 columns but it is increasing the number of rows hence affecting other dash boards

[Vijay_A_Verma · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvzSkpqlTSUTIAYkM4HasTrRScmlicmJuqkF+SkVqEpAKiCqSCOBmELLKMIQomzr7i/Mqk1MS8YhSnwrTDJBWKU0HGIFluADegAOJdBXTnITxdXJqXlpNfnlqEMMYQQxE23wEtiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Preferred valuchains" = _t, Sesame = _t, Soybeans = _t, Sunflower = _t, Poultry = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Preferred valuchains", type text}, {"Sesame", Int64.Type}, {"Soybeans", Int64.Type}, {"Sunflower", Int64.Type}, {"Poultry", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum({[Sesame],[Soybeans],[Sunflower],[Poultry]})),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Preferred valuchains"}, {{"Final Sum", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)