Power BI dashboard

[ATHULVADAKKATH]

Dear Team,

I want to create a Power BI dashboard based on the customer response regarding for potatoo chips. 

i have one code book and response sheet. 

How I need to establish the relationship between the files and what are the steps I have to follow.

thank you

code book

Response sheet

Variable	Attribute	Type	Question/Answer	Code
Q111	Question	Codedselect	Age
			0-18	0
			18-35	1
			35-50	2
			51 or Older	3
Q112	Question	Codedselect	Have you see the add before		Aided Brand Awareness
Q112.1	Question	Codedselect	Brand 1	0
			I Have Heard Of	1
			I Have Not Heard Of	2
			Not Sure
Q112.2	Question	Codedselect	Brand 2
			I Have Heard Of	0
			I Have Not Heard Of	1
			Not Sure	2
Q112.3	Question	Codedselect	Brand 3
			I Have Heard Of	0
			I Have Not Heard Of	1
			Not Sure	2

ID	Q111	Q112.1	Q112.2	Q112.3
0	0	2	2	0
2	1	1	1	0
4	2	1	1	0
6	3	2	2	0
8	3	1	1	0
10	2	1	1	0
15	1	2	2	0
19	0	1	1	0
20	1	2	2	0
22	2	1	1	0
30	3	2	2	0
32	0	2	2	0
35	1	2	2	2
36	2	1	1	0
37	3	1	1	0
38	3	1	1	0
40	2	2	2	0
41	0	2	2	0
44	1	1	1	0
45	2	2	2	0
48	3	1	1	2
52	3	2	2	0
53	2	1	1	0
56	1	2	2	0

[v-kkf-msft · Accepted solution]

Hi @ATHULVADAKKATH ,

First for the Response table, unpivot these variable columns.

For the code book table, use the following code to transform.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZE9C8IwEIb/ytHZSpMQ6KoudbEUx9IhmqsKpYE0Ffz3Jn61NDVuDuFy3BvueUhZRhAtJqdalFFBCLFN0WNnLqq1142SKDts8GhstzrhJzt+ncQkdcUbkDRm3FVvwnjME1upN+EElIa8kahd7g1GQ2CZuCLcVA8dIpgzgpASDlgrjWM5ugzqrbVoJRCYVdnCY0mGQkvI61mpV2anzDjnK7rAvp+yBQ2fbHT4qzDbV/4Jm+8wYqMDG/vNxv6MVt0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Variable = _t, Attribute = _t, Type = _t, #"Question/Answer" = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variable", type text}, {"Attribute", type text}, {"Type", type text}, {"Question/Answer", type text}, {"Code", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute", "Type"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ",null,Replacer.ReplaceValue,{"Variable"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Variable"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Variable] <> null))
in
    #"Filtered Rows"

Then, merge the two tables.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.