Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

What steps to transform data for visualization?

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_idquiz_idQuestion_NoQuestion_URLStudent_ID 11) Is this question clear? 12) Is this confusing? 1Check all applicable: 1Comment: Not Sure 1Comment: Other 13) Is this from the current syllabus chapter ? 1Check all applicable: 12Comment: Not Sure 13Comment: Other 14comment 1Student_ID 21) Is this question clear? 12) Is this confusing? 1Check all applicable: 1Comment: Not Sure 1Comment: Other 13) Is this from the current syllabus chapter ? 1Check all applicable: 12Comment: Not Sure 13Comment: Other 14comment 1Student_ID 31) Is this question clear? 12) Is this confusing? 1Check all applicable: 1Comment: Not Sure 1Comment: Other 13) Is this from the current syllabus chapter ? 1Check all applicable: 12Comment: Not Sure 13Comment: Other 14comment 1Student_ID 41) Is this question clear? 12) Is this confusing? 1Check all applicable: 1Comment: Not Sure 1Comment: Other 13) Is this from the current syllabus chapter ? 1Check all applicable: 12Comment: Not Sure 13Comment: Other 14comment 1Student_ID 51) Is this question clear? 12) Is this confusing? 1Check all applicable: 1Comment: Not Sure 1Comment: Other 13) Is this from the current syllabus chapter ? 1Check all applicable: 12Comment: Not Sure 13Comment: Other 14comment 1Auditor: 1) Is this question clear? 1Auditor: 2) Is this confusing? 1Auditor: Check all applicable: 1Auditor: Comment: Not Sure 1Auditor: Comment: Other 1Auditor: 3) Is this from the current syllabus chapter ? 1Auditor: Check all applicable: 12Auditor: Comment: Not Sure 13Auditor: Comment: Other 14Auditor: comment 1
1901115210http://265595NN   Yprobability   472836NN   YCouldn't tell, vague   31638Y    Y    212468NN   YCouldn't tell, vague   454812NN   Yprobability   NY   Y    
1901115311http://265595NYComplex, Unclear language  N    472836NYToo long  N    31638Y    Y    212468NYToo long  N    454812NYComplex, Unclear language  N              
1901115412http://265595NN   Yprobability, thereom, statistics   472836NN   YCouldn't tell, More than 1 Chapter   31638Y    Y    212468NN   YCouldn't tell, More than 1 Chapter   454812NN   Yprobability, thereom, statistics   NY   Y    
1901115513http://265595NYComplex  N    472836NYToo longit was just a little hard so it could be ok N    31638Y    Y    212468NYToo longit was just a little hard so it could be ok N    454812NYComplex  N              
1901115614http://265595NN   Ythereom   472836NN   Y    31638Y    Y    212468NN   Y    454812NN   Ythereom             

 

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.  

Need Help

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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]

    What-steps-to-transform-data-for-visualization

    What-steps-to-transform-data-for-visualization2

     

     

    Best Regards,