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 at completed programme (and where they have't completed programme then 0) ? 

 

 

I've seen some examples using the add custom column but they don't make sense and the syntax isn't accepted when I try to add to the Custom column formula box.

 

Any help appreciated.

Thx

Need Help

2 Replies

  • 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"
  • samwar14's avatar
    samwar14
    Frequent Visitor
    2 years ago

    Hi ImkeF, thanks for your suggestion. The fabric community emails went straight to my junk folder so I didn't see your reply. I deciced to make the calculation within the SQL query and then imported it in the main table. Yes there is always a max. 2 per studen and your solution seems interesting. I might give it a go as an alternative just out of interest. Thanks again.