Forum Discussion
Mutiple answer question in power BI
Dears,
please i need your help where i am not sure how to visualize multiple answers question, where each respondent has more than answer that were entered in a different column, for ex answer 1 in col 1 and answer 2 in col 2,etc.
but they are different answers and i need to combine them to visualize this question, please advise what the best way/chart is to do it.
Many Thanks
6 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - lbendlin
here's how you transpose/unpivot your data to make it usable. This will work for any number of columns.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFBCoAgEIXhq4jrFs6MWp1FvP810lQw+heSfrxheFSKlxD84aUd937r0a2/bZpN03bXaTrNIBfBEuzIkDthxwWz92ZumAQwAdt7LDPIRbAEs3uPZSfYBbO9R/yaQg8VyCnk7L9DoYcmsAw71v8Y2Vof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, F2a.Code1 = _t, F2a.Code2 = _t, F2a.Codes3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")) in #"Filtered Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Thank you Ibendin so much for your help and support,it is working fine for this specifc question but for others questions with the same concept it didn't work
for example i have another question with the same structure : ID,column name (Q8_code_1,Q8_code_2,Q8_code_3,Q8_code_4) and the codes that was inserted not codes but text for example (lack of trainings,no clear career path,low salries,...) how can i edit the code to work on the similar concept taking into the consideration the sample size is different from question to another
one question is 780 another is 130 etc
many thanks again
