Survey Data model

[lifeofthenoobie]

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

RespID	Age	Loc	Gen	Prd	Brd	Q1	Q2	Q3	Q4	Q5
1	1	4	1	1	3	1	1	1	2	4
2	1	2	2	2	1	5	2	2	3	3
3	3	1	2	3	2	6	1	3	4	2
4	4	3	2	3	2	3	7	4	1	5
5	1	4	1	1	1	2	7	7	1	3
6	2	2	1	2	2	4	7	7	2	2
7	2	1	2	2	1	5	3	6	4	7
8	3	3	1	3	1	2	4	5	6	7
9	4	3	2	1	3	1	5	5	7	6

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

[amitchandak]

@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"