finding number of questions from office 365 form output

[wilson_smyth]

Once an O365 forms survey is complete, the output is exported in excel format.

Its in a pretty nasty format for analysis however, a single row per person who completed the form, and a column per question.

This becomes an issue when tying to identify the number of questions on the form based only on this output & when combining the output of multiple forms with different numbers of questions.

Im providing sample data based on a quiz. Two files are the output  of quizes done via O365 forms. Each quiz had a different number of questions.

The main questions i have are:

• How do i calculate the number of questions per quiz with only this information?
• Is it possible to pivot this so as to have a row per question per user, but get the users answer and if they scored a point all in the one row?

Link to dataset - 2 excel files:

https://1drv.ms/u/s!AgldA0VQfPV9hNpdHenhQ790Q6km7w?e=hU6A7e 

[v-eachen-msft · Accepted solution]

Hi @wilson_smyth ,

You could unpivot your question columns like the following image:

Then these tables have the same columns and you could append them.

Use COUNT() or COUNTROWS() to get the question numbers of every ID.