Forum Discussion

lifeofthenoobie's avatar
lifeofthenoobie
Frequent Visitor
2 years ago

Survey Data model

Hello PowerBI developers!
I am in my first DE project and I will be giving my fellow PBI Devs survey data. My question is: how would you like it to be served?

 

The raw data is in xlsx format, with two worksheets. One with numerical data, and one with descriptions what is hidden behind the numbers as well as Question's

This is sample of the data

RespIDAgeLocGenPrdBrdQ1Q2Q3Q4Q5
11411311124
21222152233
33123261342
44323237415
51411127713
62212247722
72122153647
83313124567
94321315576

and in the second worksheet i'd have something like
RespID - RespondentID
Age - Age of respondent
Loc - Location of respondent
PRD - Product
..
Q1 - In a scale of 1-7 how do you like XX
then values
age: 1 - '19-24' .. 4 - '60+'
gender: 1-'female',2-'male'

Q1-Q5: 1 - very much 2- not so much 7 - not at all
etc

So I was thinking of:
pivot the columns Q1-Q5, so I have them in rows
create dim tables for should I separate age, location, gender, product, brand. Also dim_question?

 

My obstacles:
Should I create separate table with the scale of 1-7 for Q1-Q5? What with 1,2,3 values from location, gender? Will it overlap?

 

How can I add other countries? Assuming that the schema is the same, I can append and then create artificial ID incrementally from first to last country, so for example USA will have 10 respondents therefore 1-10 ID will be USA's, GB 20 resp so 11-20 will be GB etc

 

In other words - how can I distinguish the countries? Lets say RespID = 1 from that survey (GB) will not equal to RespID = 1 of USA survey. Also the age buckets could be different, questions or its order etc

 

Thank you for any tips

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    lifeofthenoobie , I would unpivot the questions columns

    Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

     

    try this in a blank query in Power Query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVBJDgAhCPuLZy+KyMxbjP//xqQIhjEeGhZbKI6RSsqKZhGgkANV32cemnnHgZpDTQqwKczyF8Qe9jTtgd2sooONKMEhK5svvn2PGMp20g+/nrfArtuJXJh+JZn/pQT72Tf/f8/nsykW+z2ujAo2iCrm/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RespID = _t, Age = _t, Loc = _t, Gen = _t, Prd = _t, Brd = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t, Q5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RespID", Int64.Type}, {"Age", Int64.Type}, {"Loc", Int64.Type}, {"Gen", Int64.Type}, {"Prd", Int64.Type}, {"Brd", Int64.Type}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}, {"Q5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Brd", "Prd", "Gen", "Loc", "Age", "RespID"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"