What steps to transform data for visualization?

[Anonymous]

I have a survey of 5 students answering the same question about 100 quiz questions and an auditor that also answers the questions for a sample of the questions.  See sample of data below:

source_id

quiz_id

Question_No

Question_URL

Student_ID 1

1) Is this question clear? 1

2) Is this confusing? 1

Check all applicable: 1

Comment: Not Sure 1

Comment: Other 1

3) Is this from the current syllabus chapter ? 1

Check all applicable: 12

Comment: Not Sure 13

Comment: Other 14

comment 1

Student_ID 2

1) Is this question clear? 1

2) Is this confusing? 1

Check all applicable: 1

Comment: Not Sure 1

Comment: Other 1

3) Is this from the current syllabus chapter ? 1

Check all applicable: 12

Comment: Not Sure 13

Comment: Other 14

comment 1

Student_ID 3

1) Is this question clear? 1

2) Is this confusing? 1

Check all applicable: 1

Comment: Not Sure 1

Comment: Other 1

3) Is this from the current syllabus chapter ? 1

Check all applicable: 12

Comment: Not Sure 13

Comment: Other 14

comment 1

Student_ID 4

1) Is this question clear? 1

2) Is this confusing? 1

Check all applicable: 1

Comment: Not Sure 1

Comment: Other 1

3) Is this from the current syllabus chapter ? 1

Check all applicable: 12

Comment: Not Sure 13

Comment: Other 14

comment 1

Student_ID 5

1) Is this question clear? 1

2) Is this confusing? 1

Check all applicable: 1

Comment: Not Sure 1

Comment: Other 1

3) Is this from the current syllabus chapter ? 1

Check all applicable: 12

Comment: Not Sure 13

Comment: Other 14

comment 1

Auditor: 1) Is this question clear? 1

Auditor: 2) Is this confusing? 1

Auditor: Check all applicable: 1

Auditor: Comment: Not Sure 1

Auditor: Comment: Other 1

Auditor: 3) Is this from the current syllabus chapter ? 1

Auditor: Check all applicable: 12

Auditor: Comment: Not Sure 13

Auditor: Comment: Other 14

Auditor: comment 1

19011152

1

0

http://

265595

N

N

Y

probability

472836

N

N

Y

Couldn't tell, vague

31638

Y

Y

212468

N

N

Y

Couldn't tell, vague

454812

N

N

Y

probability

N

Y

Y

19011153

1

1

http://

265595

N

Y

Complex, Unclear language

N

472836

N

Y

Too long

N

31638

Y

Y

212468

N

Y

Too long

N

454812

N

Y

Complex, Unclear language

N

19011154

1

2

http://

265595

N

N

Y

probability, thereom, statistics

472836

N

N

Y

Couldn't tell, More than 1 Chapter

31638

Y

Y

212468

N

N

Y

Couldn't tell, More than 1 Chapter

454812

N

N

Y

probability, thereom, statistics

N

Y

Y

19011155

1

3

http://

265595

N

Y

Complex

N

472836

N

Y

Too long

it was just a little hard so it could be ok

N

31638

Y

Y

212468

N

Y

Too long

it was just a little hard so it could be ok

N

454812

N

Y

Complex

N

19011156

1

4

http://

265595

N

N

Y

thereom

472836

N

N

Y

31638

Y

Y

212468

N

N

Y

454812

N

N

Y

thereom

I need to be able to assess the following:

% of agreement where 5 students agree (4 out of 5) on the following for each quiz question: (each question should be clear OR if not clear either confusing or from a different chapter)

• Is question clear 
• Is question confusing
• Is not question from current syllabus chapter

% where 3 students agree (3 out of 5) on the above?

% remaining disagreement questions where less than 3 students agree?

The final consensus for each question =  either 4 out of 5 OR if 3 or less students have consensus than the Auditor answer.

% of students citing categories in "check all applicable" questions: across all 100 questions (pie chart)

ex.

2% complex

6% probability

etc.

I have gotten as far as splitting the columns for the comma delimited "check all that apply" columns but not sure how to transform to get to the above.  

[v-jiascu-msft]

Hi @Anonymous,

Please download the demo from the attachment. You can click the steps to see details if there are some.

if Text.Start([Attribute], 7) = "Auditor" then "Auditor" 
else if Text.Start([Attribute], 7) = "Student" then [Value] 
else let index = [Index] in Table.Max(Table.SelectRows(#"Added Index", each [Index] < index and Text.Start([Attribute], 7) = "Student"), "Index")[Value]

Best Regards,