Forum Discussion

samwar14's avatar
samwar14
Frequent Visitor
3 years ago

New column that subtracts row values based on another column

I'd like to review the improvement in test scores for each students based on the test stage, how do I create the column Improvement below where the Improvement = score at initial assessment - score a...
Need Help
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
3 years ago

Hi samwar14 ,
if there is alway max. 2 values per student, then you could:
1) pivot on [Stage] with no aggregation on value column [Score]

2) Fill down in column [Score]

This will return the starting values in all rows with ending values.

3) Add custom column where you subtract the starting value from the ending value 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjIPbMyyxRitWBCBoBsQkQV4BFQNKmQGyArMwIqg9D0ASqGqg3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Score = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Score", Int64.Type}, {"Column5", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column5]), "Column5", "Score"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Init"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Filled Down", "Subtraction", each [x] - [Init], type number)
in
    #"Inserted Subtraction"