Forum Discussion
Nart
5 years agoFrequent Visitor
Segmenting a query with so many columns (data from a long questionnaire)
I have a 700x800 dataset coming from a long questionnaire (700 questions x 800 submissions). To work with the data in Power BI, I need to unpivot every few columns, that represent a question, togeth...
- 5 years ago
Hi, Nart , you're only one inch away from goal. After unpivoting the dataset, extract the serial no of questions. i.e. "Q1" from "Q1-A1", "Q1-A2", "Q1-A3" ... a transformed dataset is like this and it's ready for slice and dice from quite a few dimensions, such as index, gender, age, question, etc.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIFYkMzIOEM4hmAeFAamW1IBBvBj9WJVjICstxAPAuQ2UYEzMNlrwGGepDZxlCzjYxgZmMzC7vbsPMRZpvAwsQSZLYxHrOwmYnbj7GxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, gender = _t, age = _t, camp = _t, #"Q1-A1" = _t, #"Q1-A2" = _t, #"Q1-A3" = _t, #"Q1-A4" = _t, #"Q1-A5" = _t, #"Q2-A1" = _t, #"Q2-A2" = _t, #"Q2-A3" = _t, #"Q2-A4" = _t, #"Q2-A5" = _t, #"Q2-A6" = _t, #"Q3-A1" = _t, #"Q3-A2" = _t, #"Q3-A3" = _t, #"Q4-A1" = _t, #"Q4-A2" = _t, #"Q4-A3" = _t, #"Q4-A4" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Index", "gender", "age", "camp"}, "Opt", "Answer"), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Question", each Text.BeforeDelimiter([Opt], "-"), type text), #"Changed Type" = Table.TransformColumnTypes(#"Inserted Text Before Delimiter",{{"Answer", Int64.Type}, {"Index", Int64.Type}}) in #"Changed Type"
Icey
Community Support
5 years agoHi Nart ,
Please let us know if the replies above are helpful.
If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let us know.
Best Regards,
Icey