Forum Discussion
Mutiple answer question in power BI
i have sent a sample data where i want to display and visualize F2a (after merging the codes) and keep the sample size as it is 28 so the total should me more than 100% since it 's a multiple answer question duer to each respondent might give more than one answer
Many thanks
Noha
Super Userhere'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
- lbendlin
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.