Questions on Data Format for Analysis

[alvin1999]

Hi all,

My question is regarding the data format and analysis ideas. Kindly put my post to the right channel if it is incorrect. 

I have a sample dataset regarding feedback from students on selected subject and the subject's teacher to their students. 

When the students and teachers submit their feedback, maximum 2 feedbacks is allowed and they have the option of not giving any feedback thus NA value will appear. The feedback is recorded into an excel. 

My question - what is the suggested data format to import to Power BI for analysis as I find it no objective for displaying the count of each feedback from students and teacher in 2 separate bar chart. Any objectives that I can consider to constuct a storyline when I form my visual in Power BI?

Here is the link of the dataset

https://docs.google.com/spreadsheets/d/1yC39eR3SoB3tGbNaDPJtu3xU2ylNdOEj/edit?usp=sharing&ouid=11028...

[saurav_0101 · Accepted solution]

You can consider using a "normalized" format with the following approach:

Suggested Data Structure
1. Entity Column: Add a column to identify the source of feedback, i.e., whether it’s from a "Student" or "Teacher".
2. Feedback Index Column: Use another column to indicate the feedback instance (e.g., "Feedback 1" or "Feedback 2").
3. Feedback Value Column: Place the feedback values in a single column rather than having separate columns for Feedback 1 and Feedback 2.

Example Structure

Subject	Entity	Feedback Index	Feedback Value
Math	Student	Feedback 1	4
Math	Student	Feedback 2	3
Math	Teacher	Feedback 1	5
Math	Teacher	Feedback 2	N/A

With this format:
- You can easily filter by "Student" or "Teacher" in Power BI.
- To calculate the total feedback count for each entity, you’d only need to count non-null entries in the "Feedback Value" column, grouped by the "Entity" column.

This normalized structure makes it easier to analyze each entity’s feedback separately and helps create flexible Power BI visuals based on user requirements.

[Anonymous · Accepted solution]

Hi @alvin1999 ,

Store each feedback record separately, rather than combining multiple feedbacks into the same row. This allows for easier statistics and analysis. The following is a suggested format:

Analyzing Ideas:

• Count the number of feedbacks: Use COUNT or COUNTROWS function to count the number of records whose Feedback Type is Student and Teacher respectively.
• Feedback Distribution Analysis: Create bar charts or pie charts to show the distribution of feedback from students and teachers respectively.
• Time Trend Analysis: Create line charts to show the trend of feedback changes in different time periods.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saurav_0101 · Accepted solution]

You can consider using a "normalized" format with the following approach:

Suggested Data Structure
1. Entity Column: Add a column to identify the source of feedback, i.e., whether it’s from a "Student" or "Teacher".
2. Feedback Index Column: Use another column to indicate the feedback instance (e.g., "Feedback 1" or "Feedback 2").
3. Feedback Value Column: Place the feedback values in a single column rather than having separate columns for Feedback 1 and Feedback 2.

Example Structure

Subject	Entity	Feedback Index	Feedback Value
Math	Student	Feedback 1	4
Math	Student	Feedback 2	3
Math	Teacher	Feedback 1	5
Math	Teacher	Feedback 2	N/A

With this format:
- You can easily filter by "Student" or "Teacher" in Power BI.
- To calculate the total feedback count for each entity, you’d only need to count non-null entries in the "Feedback Value" column, grouped by the "Entity" column.

This normalized structure makes it easier to analyze each entity’s feedback separately and helps create flexible Power BI visuals based on user requirements.

[danextian]

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"

[saurav_0101]

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.

[alvin1999]

Hi @saurav_0101 ,

Thank your for your suggestion.

For data formating suggestion number 1 -Restructure Feedback Field, by having first feedback of each party to the feedback 1 column and second feedback to feedback 2 column, similar to student and teacher may cause that I am unable to sum the number of feedback provided by student and teacher separately.

Do you have better way to get the total feedback for student and teacher separately?