Questions on Data Format for Analysis

To analyze your dataset effectively in Power BI, a few formatting adjustments and storyline ideas can enhance the visualization:

Data Formatting Suggestions:

1. Restructure Feedback Fields: Since each student and teacher can give up to two feedback entries, arrange the feedback fields into separate columns for each person’s response (e.g., Student_Feedback_1, Student_Feedback_2, Teacher_Feedback_1, Teacher_Feedback_2). This way, Power BI can calculate and compare responses easily.

2. Create a Unified Feedback Column:

   • Use Power Query to unpivot the feedback columns, creating one unified feedback column with corresponding categories (Student or Teacher) to make it easier to analyze trends across different groups.

3. Handle NA Values: Replace NA values with a blank or "No Response" to distinguish between feedback given and not given. This will help you analyze participation rates.

Analysis and Storyline Ideas:

1. Feedback Participation Rates:

   • Display the count of feedbacks given vs. not given for students and teachers, using a bar chart to show participation rates.

2. Feedback Comparison:

   • Analyze and compare the types of feedback provided by students and teachers, perhaps using a clustered bar chart to see patterns in responses.

3. Sentiment Analysis (if feedback has a text component):

   • If feedback includes qualitative text, consider a word cloud or sentiment analysis visual to identify recurring themes or sentiments in feedback.

4. Overall Feedback Trends:

   • A stacked column chart can illustrate the distribution of feedback types or sentiment by subject, showing areas with higher or lower engagement or satisfaction.

Please leave a Kudos or Mark as Solution if it helps.

Hi alvin1999 

I would add an index column which will serve as the record id and then unpivot the feedback columns as shown in the screenshot below:

Keeping the two column means, you'll have to create a measure each for Student and Teacher. If unpivoted, you can just have a single measure (distinct count of index) and be able to slice the feedback by Good/bad/etc and be able to filter which feedback goes to teacher/student.

Here's a sample M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDBC8IgFMb/F8+CT6duHVeMuhRBl2DsEDUqGBOWh/78nulE6TIC9cPH+/G997UtOSkATijZX+wDpcZ71gAKFRQDyQSIAj9bY26zdNRzAgvNeMd34zHtOkEzKGeseV/7YehH+wcbLA91hFx193zhu/YQB1Rehjml76bkaMwUIRmdwnKqQhWCoVmAfp0WQOlqCemSqyebzOiaoWBQZTMmiI5rhSzkyq3FGZ58wizCBdg3CO/VfQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, Subject = _t, Class = _t, #"Teacher ID" = _t, Date = _t, #"Feedback-student" = _t, #"Feedback-Teacher" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Student ID", type text}, {"Subject", type text}, {"Class", type text}, {"Teacher ID", type text}, {"Date", type date}, {"Feedback-student", type text}, {"Feedback-Teacher", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index", "Student ID", "Subject", "Class", "Teacher ID", "Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Feedback Category"}, {"Value", "Feedback"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Feedback Category", each Text.AfterDelimiter(_, "-"), type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Feedback Category", Text.Proper, type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Capitalized Each Word",{"Index", "Student ID", "Subject", "Class", "Teacher ID", "Date", "Feedback Category", "Feedback"})
in
    #"Reordered Columns"