Reconfigure Data from Microsoft Forms in BI

[Anonymous]

Hello,

I am trying to use my results from Microsoft Forms and having some difficulty getting the results into the formatting that 

There are several questions on my survey that ask  what product they use (out of a finite list) for a certain activity and then rate the product for how well it accomplishes the activity out of a scale of 1-10.

The data comes out looking somewhat like this:

Response Number	Question 1	Rating 1	Question 2	Rating 2	Question 3	Rating 3
12345	Product A	5	Product D	8	Product B	6
54321	Product A	8	Product A	7	Product A	8
23451	Product C	4	Product C	3	Product D	4
34512	Product B	7	Product A	5	Product A	5
45123	Product A	6	Product A	7	Product C	3

I would like to summarize the data however to just determine for each product, what the overall average score is for each question is like below:

Product	Question 1	Question 2	Question 3
Product A	6.333333	6.333333	6.5
Product B	7		6
Product C	4	3	3
Product D		8	4

I've been having difficulty determining how to do this cleanly besides just straight up reformatting my data. 

Does anyone have an idea of how to best accomplish this?

[parry2k · Accepted solution]

@Anonymous check if this blog post helps.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

thanks @parry2k that seemed to be exactly what I needed!