Forum Discussion
Segmenting a query with so many columns (data from a long questionnaire)
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"
Community ChampionHi, Nart
How do you define correlations among questions? I think another table is necessary for the dataset regardless of its form (one-dimenstional table after unpivoting or original two-dimensional table).
As you know, DAX runs on VertiPaq Engine, which is in-memory columnar database. Here's a brief introduction for your reference.
https://www.microsoftpressstore.com/articles/article.aspx?p=2449192&seqNum=2
To my superfacial understanding, VertiPaq favors one-d table.
"...
Single-column access is very fast, because it reads a single block of memory, and then it computes whatever aggregation you need on that memory block.
If an expression uses many columns, the algorithm is more complex because it requires the engine to access different memory areas at different times, keeping track of the progress in some temporary area.
The more columns you need to compute an expression, the harder it becomes to produce a final value, up to a point where it is easier to rebuild the row storage out of the column store to compute the expression.
..."
(qutotation from afore-mentioned article)
