Forum Discussion
Mutiple answer question in power BI
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.