Forum Discussion
Questions on Data Format for Analysis
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.
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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"