Create report in Power BI based on Microsoft Forms survey data

[Joakim_gg]

Hi all,

I've created a survey in Microsoft Forms which I have connected to Power BI. I've previously analyzed the data in Excel, but to make it more dynamic I'd like to create a report in Power BI instead. 

The survey consist of: 

- 50 questions (likert scale).

- 7 Dimensions (e.g. each dimension contain a set of questions)

	disagree	Disagree	Agree	agree
Score for positive items	1	2	3	4
Score for reversed items	4	3	2	1

Some of the questions in the survey are negatively formulated. Meaning, that they use a reversed version of the likert scale. 

	Positively formulated items	Reversed formulated items
Dimension 1- management safety priority and ability (9 items):	A1, A2, A4, A6, A7	A3, A5, A8, A9
Dimension 2 – management safety empowerment (7 items):	A10, A11, A12, A14, A16	A13, A15
Dimension 3 – management safety justice (6 items):	A17, A19, A20, A22	A18, A21
Dimension 4 – workers’ safety commitment (6 items):	A23, A24, A27	A25, A26, A28
Dimension 5 - workers’ safety priority and risk non-acceptance (7 items):	A33	A29, A30, A31, A32, A34, A35
Dimension 6 – Peer safety communication learning, and trust in safety ability (8 items):	A36, A37, A38, A39, A40, A42, A43	A41
Dimension 7 – workers’ trust in efficacy of safety systems (7 items):	A44, A46, A48, A50	A45, A47, A49

The mean score for each respondent is calculated with the following formula: 

(A1+A2+(4-A3)+A4+(4-A5)+A6+A7+(4-A8)+(4-A9)) / Number of answered items

The question is if it's possible to replicate this in Power BI? It would make the management of it quite easier as I could set a dynamic refresh of the report. 

[v-jingzhang]

Hi @Joakim_gg 

Please download the attachment to see the transformation steps in Power Query Editor. I add two tables ScoreProperty and QuestionProperty in it. In Positively formulated column, 1 represents positively formulated and -1 represents negatively formulated. 

Let me know if you have any questions. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Joakim_gg]

							3	1. Management safety priority and ability						2. Management safety empowerment					3. Management safety justice					4. Workers' safety commitment				5. Workers safety priority and risk non-acceptance				6. Safety communication , learning and trust in co-workers safety competence		7. Workers trust in efficiency of safety systems
Number	country	branch	site	group	A	B	C	a1	a2	a3r	a4	a5r	a6	a7	a8r	a9r	M	a10	a11	a12	a13r	a14	a15r	a16	M	a17	a18r	a19	a20	a21r	a22	M	a23	a24	a25r	a26r	a27	a28r	M	a29r	a30r	a31r	a32r	a33	a34r	a35r	M	a36	a37	a38	a39	a40	a41r	a42	a43	M	a44	a45r	a46	a47r	a48	a49r	a50	M
1								2	2	3	1	4	1	4	4	3	1,777778	1	4	4	4	4	4	2	2,428571	3	1	1	2	4	4	2,5	1	3	4	4	4	4	1,833333	1	2	2	1	1	2	3	2,857143	2	2	2	2	2	2	4	4	2,625	2	2	4	4	4	1	4	3,142857
2								2	2	3	3	3	2	2	2	2	2,333333	3	4	4	3	2	3	2	2,714286	4	3	3	3	4	4	2,833333	1	1	2	3	3	4	1,833333	2	4	3	3	4	2	1	2,714286	2	2	3	2	3	4	3	2	2,25	2	3	2	2	2	3	2	2,142857
3